locked
How to include only specific columns when using Include. RRS feed

  • Question

  • User2017976923 posted

    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);
            }

    Tuesday, January 5, 2021 8:45 PM

Answers

  • User1686398519 posted

    Hi Java Man, 

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 6, 2021 8:05 AM

All replies

  • User475983607 posted

    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

    Tuesday, January 5, 2021 9:07 PM
  • User2017976923 posted

    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 

    Tuesday, January 5, 2021 9:23 PM
  • User475983607 posted

    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

    Tuesday, January 5, 2021 9:48 PM
  • User2017976923 posted

    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.



    Tuesday, January 5, 2021 9:49 PM
  • User475983607 posted

    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?

    Wednesday, January 6, 2021 12:27 AM
  • User2017976923 posted

    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.

    Wednesday, January 6, 2021 12:38 AM
  • User1686398519 posted

    Hi Java Man, 

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 6, 2021 8:05 AM
  • User2017976923 posted


    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.

    Thank you so much!


    Sunday, January 10, 2021 9:14 PM