Entity Framework Join 3 Tables

Question

I'm trying to join three tables but I can't understand the method...

I completed join 2 tables

        var entryPoint = dbContext.tbl_EntryPoint
            .Join(dbContext.tbl_Entry,
                c => c.EID,
                cm => cm.EID,
                (c, cm) => new
                {
                    UID = cm.OwnerUID,
                    TID = cm.TID,
                    EID = c.EID,
                }).
            Where(a => a.UID == user.UID).Take(10);

I would like to include tbl_Title table with TID PK and get Title field.

Thanks a lot

Solution 1

This is untested, but I believe the syntax should work for a lambda query. As you join more tables with this syntax you have to drill further down into the new objects to reach the values you want to manipulate.

var fullEntries = dbContext.tbl_EntryPoint
    .Join(
        dbContext.tbl_Entry,
        entryPoint => entryPoint.EID,
        entry => entry.EID,
        (entryPoint, entry) => new { entryPoint, entry }
    )
    .Join(
        dbContext.tbl_Title,
        combinedEntry => combinedEntry.entry.TID,
        title => title.TID,
        (combinedEntry, title) => new 
        {
            UID = combinedEntry.entry.OwnerUID,
            TID = combinedEntry.entry.TID,
            EID = combinedEntry.entryPoint.EID,
            Title = title.Title
        }
    )
    .Where(fullEntry => fullEntry.UID == user.UID)
    .Take(10);

Solution 2

I think it will be easier using syntax-based query:

var entryPoint = (from ep in dbContext.tbl_EntryPoint
                 join e in dbContext.tbl_Entry on ep.EID equals e.EID
                 join t in dbContext.tbl_Title on e.TID equals t.TID
                 where e.OwnerID == user.UID
                 select new {
                     UID = e.OwnerID,
                     TID = e.TID,
                     Title = t.Title,
                     EID = e.EID
                 }).Take(10);

And you should probably add orderby clause, to make sure Top(10) returns correct top ten items.

Comments

[xspydr]

Check out this related article. It's not using the Method notation but, you should be able to get the gist... stackoverflow.com/questions/11204367/…

[Gert Arnold]

Please show a picture with expanded navigation properties. Navigation properties are ready-made joins.

[user3432436]

stackoverflow.com/questions/21759590/… stackoverflow.com/questions/11204367/…

[Erçin Dedeoğlu]

Thank you very much for the method; works clear but I would like to see the answer as I asked, thanks a lot again.

[Vini]

@MarcinJuraszek : if i need a ViewModel to work , is it necessary that i join the tables?

[Pynt]

@Dan Out of curiosity is it just the not thought out at all naming conventions with the c, cm, and ccm, or simply the syntax required to perform the joins using linq and lambda that is hideous? If the former, and you'd like to edit the post to have a better layout, by all means have at it. I'm still new to entity framework and am still soaking in best practices so if you have suggestions to make this answer more eloquent for future users I'd appreciate the assistance.

[Dan Bechard]

I hadn't given the exact reason much thought when I commented, but certainly the naming conventions hurt readability (obv. copied from from OP). Also, the commas as the beginning of the line hurt readability a lot (subjective, for me), and whitespace / indentation could be slightly improved. I've submitted an edit with all of these (IMHO) improvements since you requested it.

[Dan Bechard]

Code formatting is often biased, but there are general things which most people agree look better. As for naming conventions, I used to call things really short names, but I can type plenty fast enough now (not even considering things like Intellisense) that the few characters saved is not worth the detriment in readability versus naming things verbosely e.g. "EntryID" vs. "EID", "combinedEntry" vs. "cm", etc. Eventually, someone else is going to read my code, and I'd rather they not grow hatred for me as a linear function of the number of lines of my code they've had to read / maintain.

[sandiejat]

This doesn't work even without async. I have the exact scenario but the query throws an exception [the_list_of_all_return_variables]' cannot be serialized . @marcinJuraszek - Could you have a look at stackoverflow.com/questions/42453123/…

[Auspex]

I just don't get the arguments against commas starting lines. I'm a firm believer, as it makes commenting out individual clauses/arguments really easy. And it looks prettier :-)

[Aswin Ramakrishnan]

Readability and semantics aside, this is a better answer than the usual from x in context.table join ... in my opinion. This way you can build your Joins and Where clauses dynamically and add paging and stuff later.

[SteveP]

Great answer, thank you. A point to note if your query has includes then these must be placed before the joins.

[Caius Jard]

@Auspex except it makes commenting out the first line harder ,just like putting the comma at the end makes commenting out the last line harder -so in the end there's no hardness difference .You're just left with trying to argue that starting a character sequence with punctuation looks prettier than ending a character sequence with punctuation ..Which as you can see ,from the punctuation in this comment ,is not true .

[AlbatrossCafe]

I prefer the other syntax except when it comes to joins.... the other syntax is so much more convoluted. I don't get it at all.