How to translate SQL to LINQ with individual field joins?

Question

I have a SQL statement, that I want to implement in a .NET website project. I don't understand how to join in a particular field, as the fields aren't available when I just select from the table.

My SQL

SELECT *

FROM [vResidence] c

JOIN [vJobs] j on c.UID = j.UID

This is the LINQ I have tried, but I am stuck at the 'ON' part:

results = (from j in vJobs

join cr in vResidence on ??? )

When I try 'j.', the only option I get is 'equals'.

If you have mapped the database tables correctly then you should have automatically created classes & the fields will be available - allowing for a very similar join _"j.UID==cr.UID"_ - if the fields aren't available then what are vJobs & vResidence instances of? — PaulF, Jul 17 '18 at 10:02
Also see : https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql/8251984 — PaulF, Jul 17 '18 at 10:09
I didn't know linq uses 'equals' instead of == .... thanks for the help — Cloud, Jul 17 '18 at 10:10

score 1 · Accepted Answer · answered Jul 17 '18 at 09:57

1

You can follow as this.connect to tables as JOIN use equals keyword

var result = from r in vResidence
             join j vJobs on r.UID equals  j.UID
             select new {[yourcolnum]};

answered Jul 17 '18 at 09:57

D-Shih

44,943
6
31
51

score 1 · Answer 2 · answered Jul 17 '18 at 09:57

1

You can try this

var result = (from j in vJobs
              join cr in vResidence
              on j.UID equals cr.UID
              select new {
                ...
              }).ToList();

answered Jul 17 '18 at 09:57

Aaron Tee

91
5

Drag and Drop · Answer 3 · 2018-07-17T11:27:30.240

1

The Linq expression is the following:

from t1 in Table1
join t2 in Table2
 on t1.ID equals t2.ID

The join clause on must be do in order: first the first table, then the second.
The keyword equals must be use.

edited Jul 17 '18 at 11:27

answered Jul 17 '18 at 10:01

Drag and Drop

2,672
3
25
37

1

This is not `Lambda` expression, it is again `Linq`. – SelvaS Jul 17 '18 at 11:25

score 1 · Answer 4 · answered Jul 17 '18 at 11:41

Apart from the above Linq answers, we can do JOIN using Enumerable.Join extension with Lambda expressions. Try something like,

        var result = vJobs.Join(vResidence, jb => new { jb.UID }, res => new { res.UID },
                     (jb, res) => new { jb, res })
                     .Select(x => x.jb) //Select the required properties (from both objects) with anonymous object or select left/right object
                     .ToList();

C# Fiddle with sample data.

How to translate SQL to LINQ with individual field joins?

4 Answers4