I'm trying to retrieve a list of orders with a LINQ query. The class model of OrderItems has a list of products that is declared as a foreign key referencing the Product class. When I select the order items from a LINQ query and Include the Product I still get a null value for the Product object inside the OrderItems object. This is the code below:
var orderItems = _db.OrderItems
.Where(x => x.OrderID_FKey == order.ID)
.Include(x => x.Product)
.ToList();
var products = orderItems.Select(x => new OrderProductView
{
ID = x.ProductID_FKey!.Value,
ProductNameEN = x.Product.ProductNameEN,
ProductNameKU = x.Product.ProductNameKU,
ProductNameAR = x.Product.ProductNameAR,
Quantity = x.Quantity!.Value,
SubTotal = x.SubTotal!.Value
}).ToList();
Note that I have Includeed the Product object in the LINQ query but the second query returns null for the product names that is accessed via the Product object in the OrderItems class.
I have tried to resolve the issue like the answer here by putting the virtual keyword before the foreign key reference for the Product in the OrderItems class but it doesn't change anything, I still get the NullException error.
I have also read it elsewhere that the Include method works if LazyLoading is set to false in DatabaseContext so I have also made sure that it is set to false in my project.
It would have helped if you'd given us the classes. I have to make some guesses.
You wrote:
The class model of OrderItems has a list of products that is declared as a foreign key referencing the Product class.
From this, I would assume that every OrderItem has zero or more Products. Since you use a foreign key to refer to the Product, I think you didn't mean that. It seems to me that you meant that every OrderItem is about exactly one Product.
Apparently you have Orders and OrderItems. Every Order has zero or more OrderItems, every OrderItem belongs to exactly one Order, namely the Order that the foreign key OrderID_FKey refers to. This is a straightforward one-to-many relation.
I would have thought, that every OrderItem refers to exactly one Product, namely the Product that the foreign key ProductId (or another identifier) refers to. Every Product is a Product in zero or more OrderItems. Also a one-to-many relation.
If you'd followed the entity framework conventions you would have had classes like the following:
class Order
{
public int Id {get; set;}
// every Order has zero or more OrderItems (one-to-many)
public virtual ICollection<OrderItem> OrderItems {get; set;}
... // other properties
}
class OrderItem
{
public int Id {get; set;}
// every OrderItem belongs to one Order, using foreign key
public int OrderId {get; set;}
public virtual Order Order {get; set;
// every OrderItem is about exactly one Product, using foreign key
public int ProductId {get; set;}
public virtual Product Product {get; set;}
public int Quantity {get; set;} // the number of Products in this OrderItem
public decimal SubTotal {get; set;} // = OrderItem.Quantity * Product.Price
... // other properties
}
By the way, can it be that sometimes an OrderItem does not have a Product at all? In that case, property ProductId must be nullable. So "every OrderItem belongs to zero or one Product". If this is the case, the answer is slightly different. I guess you'll get the gist.
And class Product:
class Product
{
public int Id {get; set;}
// Every Product appears in zero or more OrderItems (one-to-many)
public virtual ICollection<OrderItem> OrderItems {get; set;}
... // other properties
public string ProductNameEN {get; set;}
public string ProductNameKU {get; set;}
public string ProductNameAR {get; set;}
public decimal Price {get; set;} // the Price of one Product
}
This is enough for entity framework to know the columns of the tables and the relations between the tables. It might be that you decided to deviate from the conventions. In that case you probably needed attributes or fluent API to inform entity framework about the relations.
In entity framework the non-virtual properties refer to the columns of the tables; the virtual properties refer to the relations between the tables.
OrderId is a column in table OrderItems, hence it is a non-virtual property. Property Order is related to OrderItems, hence property Order is virtual.
Requirement: given an Order, (or the Id of an Order), give me a sequence of
OrderProductViews, oneOrderProductViewperOrderItem. Every OrderProductView has the Id of the Product that the OrderItem is about. It also has the three ProductNames and the Quantity and SubTotal of the OrderItem.
Order order = ...
int orderId = order.Id;
IQueryable<OrderProductView> = dbContext.OrderItems
.Where(orderItem => orderItem.OrderId == orderId)
.Select(orderItem => new OrderProductView
{
ProductId = orderItem.ProductId,
ProductNameEN = orderItem.Product.ProductNameEN,
ProductNameKU = orderItem.Product.ProductNameKU,
ProductNameAR = orderItem.Product.ProductNameAR,
Quantity = orderItem.Quantity,
SubTotal = orderItem.SubTotal,
})
.ToList();
In words: from the table of OrderItems, keep only those OrderItems that have a foreign key OrderId that equals orderId. From every OrderItem in the remaining sequence of OrderItems, make one new OrderProductView object.
For every new OrderProductView object we fil the properties ProductId, Quantity and SubTotal directly from the OrderItem. To fill the product names, we fetch Product that is related to this OrderItem (which is the the one and only Product that foreign key ProductId refers to)
Entity framework knows your relations. When you use the virtual properties, it knows which (Group-)Join it must do to get the requested information.
IncludeI solved the problem by using Select. You have to be aware about the difference between Select and Include.
Database management systems are extremely optimized to combine tables into a new table, and select rows and columns from this combined table. One of the slower parts is the transfer of the selected data from the DBMS to your local process.
Hence it is wise to minimize the data that is transferred. Only transfer data that you actually plan to use.
If you use Select, you define exactly what properties you want to query. When you use Include, you query all properties, including the ones that you won't use.
If you have a database of Schools and their Students (one-to-many relation), then every Student will have a foreign key to the School he attends.
If you ask for "School [4] and all its 2000 Students", you know that every Student will have a foreign key to the School with a value 4. If you use Include, then you select also the foreign keys. You'll send this value 4 over 2000 times. What a waste of processing time!
If you use Select, you can exactly specify the columns you want, and limit yourself to the data that you actually plan to use. If you don't plan to use the BirthDate of the Student, simply don't Select it.
DbContext has a ChangeTracker. Every complete row that you fetch is put in the ChangeTracker, together with a copy of it. Every item that you fetch without using Select, so also every item that you fetch by using Include is put in the ChangeTracker.
After the copy has been made, you get a reference to the copy (or the original, doesn't matter). When you alter the values of properties in the reference you got, you alter the copy in the ChangeTracker. When you call SaveChanges, every property of every copy in the ChangeTracker is comparered by value with the original. The values that are not equal are updated in the database.
So when you use include, you will transfer more properties than you probably need, but apart from that, every fetched row is also copied, and when you call SaveChanges, all copies in the ChangeTracker will be compared by value with their originals. If you query a lot of items without the intention to change them, this is a huge waste of processing power.
Always use Select whenever you query data, and Select only the properties that you actually plan to use. Only use include, only fetch complete rows, if you plan to change the fetched items.