Entity Framework: "Spoofing" the requested navigation property
I would like to "fake" a navigation property in an EF-mapped class.
Consider an example where I have books (denoted by "isbn") and purchases (denoted by "isbn" too) - note that books and purchases are intentionally NOT directly related to each other.
Now I would like to create an extension method for "Books" that returns all book purchases in the requested format, something like
public class Book
{
public virtual string isbn { get; set; }
public virtual string name { get; set; }
// Pseudo-code .. does not work, throws an exception
// "LINQ to Entities does not recognize the method [...], and this method cannot
// be translated into a store expression
public virtual IQueryable<Purchase> get_purchases(DbContext context)
{
return context.Purchases.Where(purchase => purchase.isbn == this.isbn);
}
}
public class Purchase
{
public virtual string isbn { get; set; }
public virtual double price { get; set; }
}
So in the end, I would like to do any arbitrary queries such as (completely random example)
dbContext.Books.Where(book => book.get_purchases(dbContext).Where(purchase => purchase.price > 90))
But as mentioned in the comment, my approach doesn't work as EF / LINQ cannot use my extension method.
Is there a way to do this? The operator must be translated into one SQL query; I don't want to use any methods like .ToList () to avoid unnecessary huge datasets.
Could this work by creating a function that returns
Expression<Func<Book, IQueryable<Purchase>>>
or something like that? (Couldn't try, while I'm totally terrible at writing expressions) or is there another approach?
Disclaimer: There is a good reason not to explicitly list Purchases ↔ Bonds; should not be relevant to the question, but the background is based on Floremin's answer to this question
Thank!
source to share
LINQ for objects translates LINQ queries into SQL queries, which are then sent and executed against the database (server). This is why you are getting an exception that your method get_purchases
cannot be translated into a store expression (SQL).
To get books that have been paid for over 90, you can use the LINQ Join operator:
var books = from b in dBContext.Books
join p in dBContext.Purchases on b.isbn equals p.isbn
where p.price > 90
select b;
Here is a great LINQ resource: LINQ 101
If you want to have EF-like navigation properties, such as Book.Purchases
where there is no actual link in the database, you need to create your own data repository layer that will be initialized with DbContext
that will be used in all properties and methods. Then you will use this for all data access - i.e. You will never use it DbContext
directly in your code. This is a good practice for large projects, especially if you need to implement some business rules and data manipulation before it is stored in the database. Take a look at the "Repository Pattern" and you will surely find many resources.
Here's one: Using the Repository Pattern with ASP.NET MVC and Entity Framework
source to share