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);

tables

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

Thanks a lot

Check out this related article. It's not using the Method notation but, you should be able to get the gist... http://stackoverflow.com/questions/11204367/how-to-join-multiple-tables — xspydr, Jan 10 '14 at 18:27
Please show a picture with expanded navigation properties. Navigation properties are ready-made joins. — Gert Arnold, Jan 11 '14 at 23:24
http://stackoverflow.com/questions/21759590/joining-two-tables-in-entity-framework http://stackoverflow.com/questions/11204367/how-to-join-multiple-tables — user3432436, Mar 18 '14 at 09:33

score 266 · Answer 1 · answered Jan 10 '14 at 18:30

266

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.

answered Jan 10 '14 at 18:30

MarcinJuraszek

124,003
15
196
263

5

Thank you very much for the method; works clear but I would like to see the answer as I asked, thanks a lot again. – Erçin Dedeoğlu Jan 10 '14 at 18:46
@MarcinJuraszek : if i need a ViewModel to work , is it necessary that i join the tables? – Vini Oct 08 '15 at 08:22
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 http://stackoverflow.com/questions/42453123/get-selected-values-after-joining-multiple-tables-in-entity-framework-6-using-la – sandiejat Feb 25 '17 at 07:15
2

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. – AlbatrossCafe Dec 04 '20 at 01:41

score 124 · Accepted Answer · edited Mar 22 '16 at 19:47

124

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);

edited Mar 22 '16 at 19:47

Dan Bechard

5,104
3
34
51

answered Jan 20 '15 at 15:19

Pynt

2,023
2
20
26

11

@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. – Pynt Mar 22 '16 at 17:19
4

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 Mar 22 '16 at 18:57
3

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. – Dan Bechard Mar 22 '16 at 19:11
10

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 :-) – Auspex Feb 02 '18 at 14:38
4

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. – Aswin Ramakrishnan Mar 27 '18 at 02:51
2

Great answer, thank you. A point to note if your query has includes then these must be placed before the joins. – SteveP Nov 11 '19 at 13:06
@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 . – Caius Jard Mar 31 '20 at 10:50

Entity Framework Join 3 Tables

2 Answers2

Linked