How to do join with objects in Entity Framework

Question

So I am converting a old project with ordinary SQL queries to a ORM using the Entity Framework. So I have created database model like this:

Database model

So I had this old query which I want to translate to a linq expression

SELECT UGLINK.USERNAME 
FROM GMLINK 
INNER JOIN UGLINK 
ON GMLINK.GROUPID = UGLINK.GROUPID 
WHERE (((GMLINK.MODULEID)=%ID%))

And the problem I have is that I can't figure out how to do a join query using the objects. Instead I have to go though the properties like this (which seems to be working):

// So this is one of the module objects that is located in a listView in the GUI
Module m = ModuleList.selectedItem as Module;

/* Now I want to fetch all the User objects that, 
 * via a group, is connected to a certain module */
var query = context.gmLink
    .Join(context.ugLink,
          gmlink => gmlink.GroupId,
          uglink => uglink.GroupId,
          (gmlink, uglink) => new { gmLink = gmlink, ugLink = uglink })
    .Where(gmlink => gmlink.gmLink.ModuleId == m.ModuleId)
    .Select(x => x.ugLink.User);

So as I said this works, but as you see I kind of have to connect the modules via the link tables properties .GroupId and .ModuleId and so on. Instead I would like to go through the objects created by EF.

I wanted to write a question a bit like this, but can't figure out how to do it, is it at all possible?

var query = context.User
            .Select(u => u.ugLink
                .Select(uglink => uglink.Group.gmLink
                    .Where(gmLink => gmLink.Module == m)));

Lifeless · Accepted Answer · 2014-01-10 09:13:52Z

1

This should be working:

var query = context.gmLink
    .Where(gmlink => gmlink.ModuleId == m.ModuleId)
    .SelectMany(gmlink => gmlink.Group.ugLink)
    .Select(uglink => uglink.User);

It's impossible to filter gmLinks using .Where(gmlink => gmlink.Module == m) in EF, so this comparison needs to be done using identifiers. Another option is .Where(gmlink => gmlink.Module.ModuleId == m.ModuleId)

answered Jan 10, 2014 at 9:13

Lifeless

2251 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Spikeh · Accepted Answer · 2014-01-10 09:13:57Z

1

If you have lazy loading enabled, you do not need to apply specific join notation (you can access the navigation properties directly) - but the queries that are ran against SQL are inefficient (generally the results are returned in a number of different select statements).

My preference is to disable lazy loading on the context, and use .Include() notation to join tables together manually, resulting in generally more efficient queries. .Include() is used to explicitly join entities in Entity Framework.

Join() is misleading, and not appropriate for joining tables in EF.

So, to replicate this statement:

SELECT UGLINK.USERNAME 
FROM GMLINK 
INNER JOIN UGLINK 
ON GMLINK.GROUPID = UGLINK.GROUPID 
WHERE (((GMLINK.MODULEID)=%ID%))

You would use the following:

var query = context.gmLink
    .Include(x => x.Group.gmLink)
    .Where(x => x.ModuleId == myIdVariable)
    .Select(x => new {
        UserName = x.Group.ugLink.UserName
    });

Assuming that your navigation properties are correctly set up. I have not tested this, so I'm not 100% on the syntax.

You should really run SQL profiler while you write and run LINQ to Entity queries against your database, so you can understand what's actually being generated and run against your database. A lot of the time, an EF query may be functioning correctly, but you may experience performance issues when deployed to a production system.

This whitepaper might help you out.

edited Jan 10, 2014 at 9:13

answered Jan 10, 2014 at 8:49

Spikeh

3,7454 gold badges26 silver badges51 bronze badges

5 Comments

Markus Over a year ago

Hmm, Ahh ok, that seemed very very neat, but my initial expression did only get the usernames and not the whole user. I didn't notice that. Sorry for the inconvenience but I need the User object...

Spikeh Over a year ago

Updated to output just the user object. You can project using .Select() into whatever form the predicate input allows.

Spikeh Over a year ago

Correct you are, sorry - hard to write bespoke code accurately when you don't have VS in front of you :)

Markus Over a year ago

Unfortunately that doesn't work either, since Gourp.ugLink is a List of ´ugLink's´ rather than just a ´ugLink´...

Spikeh Over a year ago

Include() is still definitely the way to go. Maybe my code is misleading as I don't fully understand your data structure.

Maarten · Accepted Answer · 2014-01-10 08:37:19Z

0

I haven't tested it, but something like this:

var users = context.User
    .Where(x => x.ugLink
        .Any(y => context.gmLink
                    .Where(z => z.ModuleId == m)
                    .Select(z => z.GroupId)
                    .Contains(y.GroupId)
        )
    )
    .ToList();

answered Jan 10, 2014 at 8:37

Maarten

23k3 gold badges51 silver badges69 bronze badges

1 Comment

Markus Over a year ago

Ok, this works with the exception that m has to be substituted with m.ModuleId, so in a sense this is better that my attempt, however it still joins on .ModuleId and .GroupId ie. the primitives and not the objects (but maybe that is the only way to go?

Collectives™ on Stack Overflow

How to do join with objects in Entity Framework

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related