C# - from SQL to Linq - Left Outer Join/Inner Join

Question

I got this in SQL:

with 3 Values: value1, value2, value3

SELECT A.a, A.b, E.c, B.d, A.e, A.f, A.g, D.h, D.i 

FROM Alpha as A 

INNER JOIN Beta as B ON A.b = B.k 

LEFT OUTER JOIN Charlie as C ON C.a = A.a  

LEFT OUTER JOIN Delta as D ON D.k = B.j  

INNER JOIN Echo as E ON A.a = E.a     

WHERE A.a = @value1 AND E.c = @value2 AND (A.a = @value3 OR @value3 = '') AND A.b = E.b

Alpha, Beta, Charlie, Delta and Echo have string a,b,c,d,e,f,g,h,i,j,k.

I've tried to convert into Linq, but I don't get the Syntax of join right. Can you show me how it should look? x)

public static List<value> GetSmthn(string value1, string value2, string value3)
{           

    return (
         from A in Alpha

         join B in Beta on A.b equals B.k

         join C in Charlie on A.a equals C.a 

         join D in Delta on B.j equals D.k 

         join E in Echo on E.a equals A.a

         where (A.a == value1 && E.c == value2 && (A.a == value3 || value3 == "") && A.b == E.b)

         select new value() { a = A.a, b = A.b, c = E.c, d = A.d, e = A.e, f = A.f, g = A.g, h = D.h i = D.i }

         ).ToList();
}

Thanks for help, maybe it's a bit confusing. Cause I changed the variable-names. Sorry for the bad english btw

You may want to check out [this MSDN](https://msdn.microsoft.com/en-us/library/bb311040.aspx) for Left Joins in linq. Looks like you'll have to use a group join and use `DefaultIfEmpty` — Timothy G., May 03 '17 at 11:26
you should check `DefaultIfEmpty` option with join. Check below link for more information related to joins. https://msdn.microsoft.com/en-us/library/bb311040.aspx — maulik kansara, May 03 '17 at 11:28
That's the problem, i don't get it on MDSN.. I don't know why, i can't understand it so im asking for help :D — R. Lorch, May 03 '17 at 11:29
it is on MSDN, Check the link i have provided and go through the Left Outer Join section. — maulik kansara, May 03 '17 at 11:29
I know the Theory, but I don't know how to use it, in my Code.. — R. Lorch, May 03 '17 at 11:32
Maybe take a look at this answer: http://stackoverflow.com/a/17142392/6530134. You are probably going to need to do it a few times. — Timothy G., May 03 '17 at 11:35

Timothy G. · Accepted Answer · 2017-05-03T12:03:00.227

3

According to MSDN:

To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. You can use null as the default value for any reference type, or you can specify a user-defined default type.

From what I can tell, you first join all the data via a GroupJoin This "correlates the elements of two sequences based on key equality and groups the results." Then, the following from specifies a default right-side element (by using DefaultIfEmpty()) to produce if a left-side element has no matches. You will need to perform this for all of your left joins:

public static List<value> GetSmthn(string value1, string value2, string value3)
    {           

    return (
         from A in Alpha

         join B in Beta on A.b equals B.k

         join C in Charlie on A.a equals C.a into cgroup
         from C in cgroup.DefaultIfEmpty()

         join D in Delta on B.j equals D.k into dgroup
         from D in dgroup.DefaultIfEmpty()

         join E in Echo on E.a equals A.a

         where (A.a == value1 && E.c == value2 && (A.a == value3 || value3 == "") && A.b == E.b)

         select new value() { a = A.a, b = A.b, c = E.c, d = A.d, e = A.e, f = A.f, g = A.g, h = D.h i = D.i }

         ).ToList();
    }

edited May 03 '17 at 12:03

answered May 03 '17 at 11:49

Timothy G.

6,335
7
30
46

why is after charlie and delta a from, and after echo not? – R. Lorch May 03 '17 at 11:54
ah ok I got it xD – R. Lorch May 03 '17 at 11:55
on the Internet-Pages is always a "new Product { Name = String.Empty, CategoryID = 0 }" or something like that, inside of DefaultIsEmpty() why do do we not need it here? – R. Lorch May 03 '17 at 11:58
@R.Lorch I was following what you've provided in your question. I also don't see the example you mention in my links. – Timothy G. May 03 '17 at 12:01
"from item in prodGroup.DefaultIfEmpty(new Product { Name = String.Empty, CategoryID = 0 })" on https://msdn.microsoft.com/en-us/library/bb311040.aspx – R. Lorch May 03 '17 at 12:03
@R.Lorch Ah I see what you mean now. If you read above: "You can use null as the default value for any reference type, **or you can specify a user-defined default type."** What they are doing is specifying a user defined default type. It's optional. In the code I have in my answer, I am using just `null`. – Timothy G. May 03 '17 at 12:05
I think sometimes to read the texts is clever^^ thanks mate:D – R. Lorch May 03 '17 at 12:07

C# - from SQL to Linq - Left Outer Join/Inner Join

1 Answers1