inner join in linq to entities

Question

I have entity called Customer and it has three properties:

public class Customer {
    public virtual Guid CompanyId;
    public virtual long Id;
    public virtual string Name;
}

I have also entity called Splitting and it has three properties:

public class Splitting {
    public virtual long CustomerId;
    public virtual long Id;
    public virtual string Name;
}

Now I need to write a method that gets companyId and customerId. The method should return list of splitting that relates to the specific customerId in the companyId. Something like this:

public IList<Splitting> get(Guid companyId, long customrId) {    
    var res=from s in Splitting 
            from c in Customer 
            ...... how to continue?

    return res.ToList();
}

In place of Splitting you have pasted Customer entity twice.. Please fix that — Ankur, May 30 '11 at 12:20
Why do u need Company ID in the method get... The Splitting has customer ID which can be used to make the selection based on passed customer ID — Ankur, May 30 '11 at 12:28
@Ankur: In order to increase security, each method that exposed by the business layer requires companyId in order to validate it effects only on its entities. — Naor, May 30 '11 at 12:33
For more complete example , please reference to http://stackoverflow.com/a/9722744/900284 — Frank Myat Thu, Nov 15 '12 at 07:20

manji · Accepted Answer · 2011-05-30T12:47:21.437

86

var res = from s in Splitting 
          join c in Customer on s.CustomerId equals c.Id
         where c.Id == customrId
            && c.CompanyId == companyId
        select s;

Using Extension methods:

var res = Splitting.Join(Customer,
                 s => s.CustomerId,
                 c => c.Id,
                 (s, c) => new { s, c })
           .Where(sc => sc.c.Id == userId && sc.c.CompanyId == companId)
           .Select(sc => sc.s);

edited May 30 '11 at 12:47

answered May 30 '11 at 12:30

manji

47,442
5
96
103

1

Is there a way to write it as lambda query? – Naor May 30 '11 at 12:35
2

If you mean linq extension methods (`Select`, `Join`... look at my edit) – manji May 30 '11 at 12:47
What about using the Extension Methods to join on multiple columns? – mighty_mite Oct 22 '21 at 20:37

score 5 · Answer 2 · edited Apr 11 '16 at 10:08

5

You can find a whole bunch of Linq examples in visual studio. Just select Help -> Samples, and then unzip the Linq samples.

Open the linq samples solution and open the LinqSamples.cs of the SampleQueries project.

The answer you are looking for is in method Linq14:

int[] numbersA = { 0, 2, 4, 5, 6, 8, 9 };
int[] numbersB = { 1, 3, 5, 7, 8 };

var pairs =
   from a in numbersA
   from b in numbersB
   where a < b
   select new {a, b};

edited Apr 11 '16 at 10:08

Mosh Feu

28,354
16
88
135

answered May 30 '11 at 12:35

RogierBessem

2,623
3
17
15

1

I never knew about getting samples directly in VS. +1 – ShooShoSha Dec 21 '16 at 21:21

aligray · Answer 3 · 2011-05-30T12:39:01.043

Not 100% sure about the relationship between these two entities but here goes:

IList<Splitting> res = (from s in [data source]
                        where s.Customer.CompanyID == [companyID] &&
                              s.CustomerID == [customerID]
                        select s).ToList();

IList<Splitting> res = [data source].Splittings.Where(
                           x => x.Customer.CompanyID == [companyID] &&
                                x.CustomerID == [customerID]).ToList();

Ankur · Answer 4 · 2011-05-30T12:43:58.570

1

public IList<Splitting> get(Guid companyId, long customrId) {    
    var res=from c in Customers_data_source
            where c.CustomerId = customrId && c.CompanyID == companyId
            from s in Splittings_data_srouce
            where s.CustomerID = c.CustomerID
            select s;

    return res.ToList();
}

edited May 30 '11 at 12:43

answered May 30 '11 at 12:36

Ankur

33,367
2
46
72

inner join in linq to entities

4 Answers4

Linked