CODEWITHSUNDEEP
c#linqjoin
johnc
johnc

Reputation: 40223

How to do joins in LINQ on multiple fields in single join

I need to do a LINQ2DataSet query that does a join on more than one field (as

var result = from x in entity
join y in entity2 
       on x.field1 = y.field1 
and 
          x.field2 = y.field2

I have yet found a suitable solution (I can add the extra constraints to a where clause, but this is far from a suitable solution, or use this solution, but that assumes an equijoin).

Is it possible in LINQ to join on multiple fields in a single join?

EDIT

var result = from x in entity
             join y in entity2
             on new { x.field1, x.field2 } equals new { y.field1, y.field2 }

is the solution I referenced as assuming an equijoin above.

Further EDIT

To answer criticism that my original example was an equijoin, I do acknowledge that, My current requirement is for an equijoin and I have already employed the solution I referenced above.

I am, however, trying to understand what possibilities and best practices I have / should employ with LINQ. I am going to need to do a Date range query join with a table ID soon, and was just pre-empting that issue, It looks like I shall have to add the date range in the where clause.

Thanks, as always, for all suggestions and comments given

Upvotes: 306

Views: 415135

Answers (13)

SyMo7amed
SyMo7amed

Reputation: 378

I used tuples to do that, this is an example for two columns :

 var list= list1.Join(list2,
                       e1 => (e1.val1,e1.val2),
                       e2 => (e2.val1,e2.val2),
                       (e1, e2) => e1).ToList();

Upvotes: 5

Adam Garner
Adam Garner

Reputation: 1276

As a full method chain that would look like this:

lista.SelectMany(a => listb.Where(xi => b.Id == a.Id && b.Total != a.Total),
                (a, b) => new ResultItem
                {
                    Id = a.Id,
                    ATotal = a.Total,
                    BTotal = b.Total
                }).ToList();

Upvotes: 3

Ven
Ven

Reputation: 1

Declare a Class(Type) to hold the elements you want to join. In the below example declare JoinElement

 public class **JoinElement**
{
    public int? Id { get; set; }
    public string Name { get; set; }

}

results = from course in courseQueryable.AsQueryable()
                  join agency in agencyQueryable.AsQueryable()
                   on new **JoinElement**() { Id = course.CourseAgencyId, Name = course.CourseDeveloper } 
                   equals new **JoinElement**() { Id = agency.CourseAgencyId, Name = "D" } into temp1

Upvotes: -3

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

I think a more readable and flexible option is to use Where function:

var result = from x in entity1
             from y in entity2
                 .Where(y => y.field1 == x.field1 && y.field2 == x.field2)

This also allows to easily change from inner join to left join by appending .DefaultIfEmpty().

Upvotes: 41

Mahesh
Mahesh

Reputation: 2757

If the field name are different in entities 

var result = from x in entity
   join y in entity2 on 
          new {
                field1=   x.field1,
               field2 =  x.field2 
             } 
          equals
         new { 
                field1= y.field1,
                field2=  y.myfield
              }
select new {x,y});

Upvotes: 7

RealNapster
RealNapster

Reputation: 1311

var result = from x in entity1
             join y in entity2
             on new { X1= x.field1, X2= x.field2 } equals new { X1=y.field1, X2= y.field2 }

You need to do this, if the column names are different in two entities.

Upvotes: 129

user3966657
user3966657

Reputation: 151

var result = from x in entity
             join y in entity2
             on new { X1= x.field1, X2= x.field2 } equals new { X1=y.field1, X2= y.field2 }
             select new 
             {
               /// Columns
              };

Upvotes: 15

niieani
niieani

Reputation: 4399

Just to complete this with an equivalent method chain syntax:

entity.Join(entity2, x => new {x.Field1, x.Field2},
                     y => new {y.Field1, y.Field2}, (x, y) => x);

While the last argument (x, y) => x is what you select (in the above case we select x).

Upvotes: 64

user2745564
user2745564

Reputation: 9

from d in db.CourseDispatches
                             join du in db.DispatchUsers on d.id equals du.dispatch_id
                             join u in db.Users on du.user_id equals u.id
                             join fr in db.Forumreports on (d.course_id + '_' + du.user_id)  equals  (fr.course_id + '_'+ fr.uid)

this works for me

Upvotes: -2

Jon Skeet
Jon Skeet

Reputation: 1499770

The solution with the anonymous type should work fine. LINQ can only represent equijoins (with join clauses, anyway), and indeed that's what you've said you want to express anyway based on your original query.

If you don't like the version with the anonymous type for some specific reason, you should explain that reason.

If you want to do something other than what you originally asked for, please give an example of what you really want to do.

EDIT: Responding to the edit in the question: yes, to do a "date range" join, you need to use a where clause instead. They're semantically equivalent really, so it's just a matter of the optimisations available. Equijoins provide simple optimisation (in LINQ to Objects, which includes LINQ to DataSets) by creating a lookup based on the inner sequence - think of it as a hashtable from key to a sequence of entries matching that key.

Doing that with date ranges is somewhat harder. However, depending on exactly what you mean by a "date range join" you may be able to do something similar - if you're planning on creating "bands" of dates (e.g. one per year) such that two entries which occur in the same year (but not on the same date) should match, then you can do it just by using that band as the key. If it's more complicated, e.g. one side of the join provides a range, and the other side of the join provides a single date, matching if it falls within that range, that would be better handled with a where clause (after a second from clause) IMO. You could do some particularly funky magic by ordering one side or the other to find matches more efficiently, but that would be a lot of work - I'd only do that kind of thing after checking whether performance is an issue.

Upvotes: 95

Perpetualcoder
Perpetualcoder

Reputation: 13571

you could do something like (below) 

var query = from p in context.T1

        join q in context.T2

        on

        new { p.Col1, p.Col2 }

        equals

         new { q.Col1, q.Col2 }

        select new {p...., q......};

Upvotes: 9

KristoferA
KristoferA

Reputation: 12397

var result = from x in entity
   join y in entity2 on new { x.field1, x.field2 } equals new { y.field1, y.field2 }

Upvotes: 169

tvanfosson
tvanfosson

Reputation: 532445

Using the join operator you can only perform equijoins. Other types of joins can be constructed using other operators. I'm not sure whether the exact join you are trying to do would be easier using these methods or by changing the where clause. Documentation on the join clause can be found here. MSDN has an article on join operations with multiple links to examples of other joins, as well.

Upvotes: 7

Related Questions