How to join linq in c#?

Question

How to convert sql to system.linq?

Select top 100 percent s.a,s.b,s.c,s.d
From table a as s, table b as x
Where 
    s.a=x.a and s.b=x.b and s.c=x.c 
Group by 
    s.a,s.b,s.c,s.d

@magnus a.k.a DISTINCT, probably because the join results in a Cartesian product. Fixing the join would be preferable — Caius Jard, Dec 06 '19 at 08:14
I cannot honestly believe, with a question title like that, that SO's own "search for similar questions" wouldn't have turned up something like https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql or a bunch of similar dupes. @user12139660 please tell us what you've tried so far that didn't work — Caius Jard, Dec 06 '19 at 08:16
@magnus specifying a TOP that is conceptually unnecessary may be an effort to achieve differences in query planning and permit tricks like order by in subqueries. The OP isnt really asking for a code review of his methods though — Caius Jard, Dec 06 '19 at 08:18
@CaiusJard I do not believe there is an equivalent of t `top 100 percent` supported by any linq to sql provider, that is why am asking. — Magnus, Dec 06 '19 at 08:20
SQL tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Dec 06 '19 at 08:34

score 0 · Answer 1 · answered Dec 06 '19 at 08:25

As per my understanding of your question; seems like you want to fetch the data in c# and do joining. if so, then you may do as following:

public class tabData
{
    public string a {get;set;}
    public string b {get;set;}
    public string c {get;set;}
    public string d {get;set;}
}

List<tabData> tabA = {data of your table a}
List<tabData> tabB = {data of your table b}

var result = from r1 in tabA
             join r2 in tabB on new {T1 = r1.a, T2 = r1.b, T3 = r1.c} equals new {T1 = r2.a, T2 = r2.b, T3 = r2.c}
             group r1 by new
                                       {
                                           aa = r1.a,
                                           bb = r1.b,
                                           cc = r1.c,
                                           dd = r1.d
                                       } into g 
            select new
                {
                    a = g.key.aa,
                    b = g.key.bb,
                    c = g.key.cc,
                    d = g.key.dd
                }

score 0 · Answer 2 · answered Dec 06 '19 at 09:05

0

I think you are asking how to join in linq as you would in sql, if so, please see below:

var query =
   from abc in tbl1
   join def in tbl2 on tbl1.PK equals tbl2.FK
select new { ABC = abc, DEF = def };

answered Dec 06 '19 at 09:05

Vishav Premlall

456
6
22

How to join linq in c#?

2 Answers2