Linq Extension method for Join c# entity-framework linq


I am in the process of learning LINQ, ASP.NET, EF, and MVC via online video tutorials. I would love some help understanding Joins in LINQ extension method syntax.

For simplification, I have two tables (these map to a SQL DB):

User Table:
    public int userID{get;set;}
    public string firstName{get;set;}

    public int ownerID{get;set;}
    public int value{get;set;}
    public string Nickname{get;set;}
    public string street{get;set;}
    public string zip{get;set;}

Let's say I want to find all the property that a particular user owns. I believe I can do something like this:

var test = db.User
    .Join(db.Address, user => user.userID, add => add.ownerID, (user, add) => new { user, add });

This should be equivalent to

SELECT * FROM User a JOIN Address b on a.userID = b.ownerID 

Please confirm that this is correct.

Now, what if I wanted to find all property that a particular user owns that has a value greater than x. Let's take it a step further and say x is a result from another LINQ query. How do I force execution of x inside of a second query? Do I even have to consider this, or will LINQ know what to do in this case?


EDIT: When I try to use the result of a query as a parameter in another, I am required to use a greedy operator to force execution. Many people like to use .Count() or .ToList(). I only expect x (from example above) to return 1 string by using .Take(1). If I append ToList() to the end of my first query, I am required to use x[0] in my second query. This seems like a messy way to do things. Is there a better way to force execution of a query when you know you will only have 1 result?

12/3/2012 10:27:35 PM

Accepted Answer

If I understand your question, you're trying to do a conditional on a joined model?

var query = db.Users.Where(x => x.Addresses.Where(y => y.Value >= yourValue).Any());

That will return all users who have a property value greater than yourValue. If you need to return the addresses with the query, you can just add Include to your query. For example:

query.Include(x => x.Addresses);

You don't need to manually do that Join that you have in your example.

12/3/2012 11:00:48 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow