In the code below I need to add the Description column from the ProductDescription table which has a FK_Product.
But I do not want to include the entire Description Table in the result set.
For example: How do I simply acquire the description column without including the entire
ProductDescription table?
You misunderstand. You can select into a new type called a projection. Otherwise the include is the type you defined which contains all the properties. You cannot change the type returned.
The LINQ docs are very good and should take a look.
I have tried everything but cannot filter out the Description.
I can use projection and filter out the Product but not the Description.
This is obvious because Product only contains a collections of Description and not individual properties.
For example, I can do this to get specific Columns in the Product table but not the Description because Product contains a collection of description and we are returning Product.
public override IEnumerable<Product> All()
{
return context.Products
.Include(d => d.ProductDescriptions).AsEnumerable().Select(x => new Product
{
Manufacturer = x.Manufacturer
});
}
So this is failing because Product does not contain individual properties for Descriptions.
public override IEnumerable<Product> All()
{
return context.Products
.Include(d => d.ProductDescriptions.Select(x => new ProductDescription
{
Description = x.Description
}));
}
Maybe I am not following but this is what makes sense here.
It seems you want to filter records which is a Where(). Select() is used to select columns not filter results. What are you trying to do? Get the Products filtered by Description where the product description is in another table? Can you provide the
schema?
I am using the word Filter loosely, I am NOT trying to filter the result.
I am trying as I initially explained to return only one Column from the ProductDescription Table but return all Columns from the Product table.
This will not work as I have discovered because the Product Model has a Collection of Descriptions and so attempting to use LINQ Projection on the Description table will not work.
This is my product Model:
public class Product
{
public Product()
{
ProductDescriptions = new HashSet<ProductDescription>();
}
[Key]
public int ProductId { get; set; }
public string Name { get; set; }
public virtual ICollection<ProductDescription> ProductDescriptions { get; set; } }
There is a One to Many relationship between Product and ProductDescription.
Thanks for taking the time to help me figure this out.
You can create a ViewModel called ProductWithDescriptionViewModel.
Create all the properties in Product and the
properties you need in ProductDescription.
Model
public class ProductWithDescriptionViewModel
{
public int ProductId { get; set; }public string Name { get; set; }public List<string> ProductDescriptionContent { get; set; }
}
public class Product
{
public Product()
{
ProductDescriptions = new HashSet<ProductDescription>();
}
[Key]
public int ProductId { get; set; }
public string Name { get; set; }
public virtual ICollection<ProductDescription> ProductDescriptions { get; set; }
}
public class ProductDescription
{
[Key]
public int ProductDescriptionId { get; set; }
public string ProductDescriptionContent { get; set; }
public string ProductDescriptionOthers { get; set; }
public int ProductId { get; set; }
[ForeignKey("ProductId")]
public Product Product { get; set; }
}
Linq
var t= context.Products.Include(m=>m.ProductDescriptions).Select(m=>new ProductWithDescriptionViewModel
{
ProductId=m.ProductId,
Name=m.Name,
ProductDescriptionContent=m.ProductDescriptions.Select(i=>i.ProductDescriptionContent).ToList()
}).ToList();
Best Regards,
YihuiSun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
You can create a ViewModel called ProductWithDescriptionViewModel.
Create all the properties in Product and the
properties you need in ProductDescription.
Thank you for posting this:
I used this strategy a lot but this was related to an API.
Either way it's very close and I think I can find a way to make this work based on your response.
Member
18 Points
35 Posts
How to include only specific columns when using Include.
Jan 05, 2021 08:45 PM|Java Man|LINK
Hey everyone:
In the code below I need to add the Description column from the ProductDescription table which has a FK_Product.
But I do not want to include the entire Description Table in the result set.
For example: How do I simply acquire the description column without including the entire ProductDescription table?
public override IEnumerable<Product> All() { return context.Products .Include(d => d.ProductDescriptions); }All-Star
52091 Points
23212 Posts
Re: How to include only specific columns when using Include.
Jan 05, 2021 09:07 PM|mgebhard|LINK
Use the "Select" method to select the fields needed in the result set.
https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/basic-linq-query-operations
Member
18 Points
35 Posts
Re: How to include only specific columns when using Include.
Jan 05, 2021 09:23 PM|Java Man|LINK
I am not certain, I did attempt that before but it did not work:
Something like this:
public override IEnumerable<Product> All() { return context.Products .Include(d => d.ProductDescriptions.Select(x => x.Description)); }Error:
is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target
All-Star
52091 Points
23212 Posts
Re: How to include only specific columns when using Include.
Jan 05, 2021 09:48 PM|mgebhard|LINK
You misunderstand. You can select into a new type called a projection. Otherwise the include is the type you defined which contains all the properties. You cannot change the type returned.
The LINQ docs are very good and should take a look.
https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/projection-operations
Member
18 Points
35 Posts
Re: How to include only specific columns when using Include.
Jan 05, 2021 09:49 PM|Java Man|LINK
I have tried everything but cannot filter out the Description.
I can use projection and filter out the Product but not the Description.
This is obvious because Product only contains a collections of Description and not individual properties.
For example, I can do this to get specific Columns in the Product table but not the Description because Product contains a collection of description and we are returning Product.
public override IEnumerable<Product> All() { return context.Products .Include(d => d.ProductDescriptions).AsEnumerable().Select(x => new Product { Manufacturer = x.Manufacturer }); }So this is failing because Product does not contain individual properties for Descriptions.
public override IEnumerable<Product> All() { return context.Products .Include(d => d.ProductDescriptions.Select(x => new ProductDescription { Description = x.Description })); }Maybe I am not following but this is what makes sense here.
All-Star
52091 Points
23212 Posts
Re: How to include only specific columns when using Include.
Jan 06, 2021 12:27 AM|mgebhard|LINK
It seems you want to filter records which is a Where(). Select() is used to select columns not filter results. What are you trying to do? Get the Products filtered by Description where the product description is in another table? Can you provide the schema?
Member
18 Points
35 Posts
Re: How to include only specific columns when using Include.
Jan 06, 2021 12:38 AM|Java Man|LINK
I am using the word Filter loosely, I am NOT trying to filter the result.
I am trying as I initially explained to return only one Column from the ProductDescription Table but return all Columns from the Product table.
This will not work as I have discovered because the Product Model has a Collection of Descriptions and so attempting to use LINQ Projection on the Description table will not work.
This is my product Model:
public class Product { public Product() { ProductDescriptions = new HashSet<ProductDescription>(); } [Key] public int ProductId { get; set; } public string Name { get; set; } public virtual ICollection<ProductDescription> ProductDescriptions { get; set; }}
There is a One to Many relationship between Product and ProductDescription.
Thanks for taking the time to help me figure this out.
Contributor
2360 Points
676 Posts
Re: How to include only specific columns when using Include.
Jan 06, 2021 08:05 AM|YihuiSun|LINK
Hi Java Man,
You can create a ViewModel called ProductWithDescriptionViewModel.
Create all the properties in Product and the properties you need in ProductDescription.
Model
Linq
var t= context.Products.Include(m=>m.ProductDescriptions).Select(m=>new ProductWithDescriptionViewModel { ProductId=m.ProductId, Name=m.Name, ProductDescriptionContent=m.ProductDescriptions.Select(i=>i.ProductDescriptionContent).ToList() }).ToList();Best Regards,
YihuiSun
Member
18 Points
35 Posts
Re: How to include only specific columns when using Include.
Jan 10, 2021 09:14 PM|Java Man|LINK
Thank you for posting this:
I used this strategy a lot but this was related to an API.
Either way it's very close and I think I can find a way to make this work based on your response.
Thank you so much!