Is there a way to include certain fields of a class in linq?

user_9110308 0
Viewed 247

My class structure is as follows. I'm trying to include the User field for the Order result. But I don't want to get the Orders property of the User class.

public class Order{
   public int OrderId { get; set; }
   public virtual User User { get; set; }
}

public class User{
   public int UserId {get; set;}
   public string Name {get; set;}
   public string SurName {get; set;}
   public virtual ICollection<Order> Orders { get; set; }
}

I wrote this code.

var orders = context.Set<Order>()
                    .Include(t => new { Name = t.User.Name, Surname = t.User.SurName })
                    .ToList();

But I get an error that

The expression 'new <>f__AnonymousType20`2(Name = (t As Order).User.Name, Surname = (t As Order).User.SurName)' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393."

linq .net c#
1 Answers

I'm trying to include the User field for the Order result.

So you want to query a sequence of Orders, every Order with several properties of the User of this Order.

var orders = dbContext.Orders
    .Where(order => ...)        // if you don't want all Orders
    .Select(order => new
    {
        // Select only the Order properties that you plan to use
        Id = order.Id,
        Total = order.Total,
        ...

        // The User of this Order
        User = new
        {
            // Select only the User properties that you plan to use
            Id = order.User.Id,
            Name = order.User.Name,
            ...
        },
    });

I use anonymous type here. If you need to return the fetched data from a procedure you need to put the data in a predefined class.

Some versions of entity framework won't support using the virtual properties. In that case you'll have to do the (Group-)Join yourself. If you start at the "one" side in a one-to-many relation, do a GroupJoin; if you start at the "many" side, a standard join will suffice. Use the overload that has a parameter resultSelector to precisely request what you want.

var orders = dbContext.Orders
    .Where(order => ...)
    .Join(dbContext.Users,

    order => order.UserId,   // from every Order take the foreign key to the user
    user => user.UserId,     // from every User take the primary key

    // Parameter resultSelector: get every Order with its one and only user
    // to make one new
    (order, userOfThisOrder) => new
    {
        Id = order.Id,
        TotalPrice = order.TotalPrice,

        User = new
        {
           Id = userOfThisOrder.UserId,
           Name = userOfThisOrder.Name,
           ...
        },
    });

Why not use include?

Include will fetch the complete row of the table, inclusive the properties that you won't use, or properties of which you already know the value.

Suppose you have a School database with Schools and Students, and a one-to-many relation with a foreign key: every School has zero or more Students, every Student attends exactly one School, namely the School that the foreign key SchoolId refers to.

Now if you fetch School [10], and use Include to fetch all its 2000 Students, then you'll fetch the foreign key SchoolId once for every fetched Student. You already know that this foreign key will have a value 10. You will be transferring this value over 2000 times. What a waste of processing power.

Another reason not to fetch complete rows, and not use Include is the following. The DbContext holds a ChangeTracker. When you fetch a complete row, the data is stored in the ChangeTracker, together with a Clone. You get the reference to the Original. Whenever you change values of properties of the retrieved data, you change the values in the Original. If you update the changed data using SaveChanges, then the original is compared by value with the clone in the ChangeTracker. Only the changed properties will be sent to the database.

So if you fetch School [10] with all its 2000 Students, and you don't plan to update the fetched data, then fetching the complete rows will fetch one School and 2000 Students. Every fetched item will be copied. And if you later fetch Student [42] to change his Address, and call SaveChanges, all fetched 2000 Students will be compared with their Clones, value by value. What a waste of processing power, if you didn't plan to update any of these 2000 Students.

When using entity framework always use Select and select only the properties that you actually plan to use. Only fetch complete rows, only use Include, if you plan to update the fetched data.

user_22817900
Related