How to Join 2 tables using lambda expression

Question

Can anyone show me how to write this sql query using lambda expression.

I am trying to join CoachingLang.ExpandedForm & CoachingLang.ExpandedCategory to CoachingDef.FormCategory.

SELECT Ef.Translation +' - '+ Ec.Translation, Fc.FormCategoryID, Ef.FormId,Ef.Translation, Ec.CategoryId, Ec.Translation
FROM CoachingDef.FormCategory Fc
INNER JOIN CoachingLang.ExpandedForm Ef ON Fc.FormID = Ef.FormId 
AND Ef.DictionaryId = -2147483645 AND Ef.PropertyId = -2147483647
INNER JOIN CoachingLang.ExpandedCategory Ec ON Fc.CategoryID = Ec.CategoryId
AND Ec.DictionaryId = -2147483645 AND Ec.PropertyId = -2147483647

i3arnon · Accepted Answer · 2014-01-02 14:12:47Z

3

from Fc in CoachingDef.FormCategory
join Ef in CoachingLang.ExpandedForm on Fc.FormID equals Ef.FormId 
join Ec in CoachingLang.ExpandedCategory on Fc.CategoryID equals Ec.CategoryId
where Ef.DictionaryId = -2147483645 && Ef.PropertyId = -2147483647
&& Ec.DictionaryId = -2147483645 && Ec.PropertyId = -2147483647

select new {Translation = Ef.Translation +' - '+ Ec.Translation, 
    Fc.FormCategoryID, Ef.FormId,Ef.Translation, 
    Ec.CategoryId, Ec.Translation}

edited Jan 2, 2014 at 14:12

i3arnon

117k33 gold badges334 silver badges358 bronze badges

answered Dec 23, 2013 at 17:10

AD.Net

13.4k2 gold badges30 silver badges47 bronze badges

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

Comments

D Stanley · Accepted Answer · 2013-12-23 17:30:13Z

Joins are messy in Lambda form, but here goes:

 CoachingDef.FormCategory
            .Join(CoachingLang.ExpandedForm,
                  Fc => new {Fc.FormID, 
                             DictionaryId = -2147483645, 
                             PropertyId = -2147483647}  // primary key
                  Ef => new {Ef.FormId, 
                             Ef.DictionaryId,
                             Ef.PropertyId}   // foreign key
                  (Fc,Ef) => new {Fc, Ef})
            .Join(CoachingLang.ExpandedCategory,   // the "left" side is now the merged Fc/Ef type
                  FcEf => new {FeEf.Fc.CategoryID, 
                               DictionaryId = -2147483645, 
                               PropertyId = -2147483647}  // primary key
                  Ec => new {Ec.CategoryId, 
                             Ec.DictionaryId,
                             Ec.PropertyId}   // foreign key
                  (FcEf,Ec) => new {FcEf.Fc, FcEf.Ef, Ec})
             .Select(FcEfEc => new {Translation = FcEfEc.Ef.Translation +' - '+ FcEfEc.Ec.Translation, 
                                    FcEfEc.Fc.FormCategoryID, 
                                    FcEfEc.Ef.FormId, 
                                    FcEfEc.Ef.Translation, 
                                    Ec.CategoryId, 
                                    Ec.Translation
                                   })

It's a little cleaner in query syntax:

from Fc in CoachingDef.FormCategory
join Ef in CoachingLang.ExpandedForm on 
    new {Fc.FormID, 
         DictionaryId = -2147483645, 
         PropertyId = -2147483647} 
    equals new { Ef.FormId, 
         Ef.DictionaryId, 
         Ef.PropertyId}  
join Ec in CoachingLang.ExpandedCategory on 
    new {Fc.CategoryID, 
         DictionaryId = -2147483645, 
         PropertyId = -2147483647}
    equals new Ec.CategoryId, 
         Ec.DictionaryId, 
         Ec.PropertyId}
select new {Translation = Ef.Translation +' - '+ Ec.Translation, 
            Fc.FormCategoryID, 
            Ef.FormId,
            Ef.Translation, 
            Ec.CategoryId, 
            Ec.Translation}

Collectives™ on Stack Overflow

How to Join 2 tables using lambda expression

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related