CODEWITHSUNDEEP
sqllinqentity-frameworkc#-4.0lambda
dIvYaNsH sInGh
dIvYaNsH sInGh

Reputation: 1993

How to Join 2 tables using lambda expression

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

Upvotes: 0

Views: 1261

Answers (2)

AD.Net
AD.Net

Reputation: 13399

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}

Upvotes: 3

D Stanley
D Stanley

Reputation: 152491

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}

Upvotes: 2

Related Questions