Linq to SQL: Multiple Joins on Multiple Columns. Is This Possible

LINQ to SQL: Multiple joins ON multiple Columns. Is this possible?

Joining on multiple columns in Linq to SQL is a little different.

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { t1.ColumnA, t1.ColumnB } equals new { t2.ColumnA, t2.ColumnB }
    ...

You have to take advantage of anonymous types and compose a type for the multiple columns you wish to compare against.

This seems confusing at first but once you get acquainted with the way the SQL is composed from the expressions it will make a lot more sense, under the covers this will generate the type of join you are looking for.

EDIT Adding example for second join based on comment.

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { A = t1.ColumnA, B = t1.ColumnB } equals new { A = t2.ColumnA, B = t2.ColumnB }
    join t3 in myTABLE1List
      on new { A = t2.ColumnA, B =  t2.ColumnB } equals new { A = t3.ColumnA, B = t3.ColumnB }
    ...

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

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.

LINQ to SQL: Left join on multiple columns

In your query you didn't do any left join.
Try this:

from p in _db.places
join v in _db.VoteLogs

//This is how you join by multiple values
on new { Id = p.Id, UserID = userId } equals new { Id = v.PlaceId, UserID = v.UserID } 
into jointData

//This is how you actually turn the join into a left-join
from jointRecord in jointData.DefaultIfEmpty()

where p.Public == 1
select new
{
    Id = p.Id,
    UserId = p.UserId,
    X = p.X,
    Y = p.Y,
    Titlu = p.Titlu,
    Descriere = p.Descriere,
    Public = p.Public,
    Votes = p.Votes,
    DateCreated = p.DateCreated,
    DateOccured = p.DateOccured,
    UserVoted = jointRecord.Vote 
    /* The row above will fail with a null reference if there is no record due to the left join. Do one of these:
       UserVoted = jointRecord ?.Vote - will give the default behavior for the type of Uservoted
       UserVoted = jointRecord == null ? string.Empty : jointRecord.Vote */
}

Join multiple columns from the same table using Linq

Join which contains not just AND expressions is possible via from x in entities.Where(x => ..). You have did that partially and made mistake in where condition.

Corrected query, looks the same as original SQL

var query = 
   from m in entities.UserInterests
   from j in entities.Jobs.Where(j => 
      m.FunctionalId != null && m.FunctionalId == j.FunctionId || 
      m.Careerlevel != null && m.Careerlevel == j.CarrerLevelId || 
      m.SalId != null && m.SalId == j.SalaryRangeId)
   join ur in entities.UserRegistrations on j.UserId equals ur.UserId
   join r in entities.EmplrRegistrations on j.UserId equals r.JobSeekerID
   where m.Status == true && m.UserId == 1
   select new { r.CompanyLogo, j.JobName, j.JobId, ur.UserId, r.JobSeekerID
                , ur.FirstName, j.JobType, j.JobCareerLevel, j.JobLocation };

var list = query.Distinct().ToList();

linq to sql join on multiple columns using lambda error

The names of the anonymous types have to match. In the first query you gave them names field1 and field2. The second query will use Soc\ ID on the first and Soc\ Code on the second while the Soc field will match up the ID name will not match the Code name. So the method syntax equivalent of your query syntax would actually be

var query= this.context.TabA.Join(
    this.context.TabB,
    s => new { field1 = s.Soc, field2 = s.ID },
    h => new { field1 = h.Soc, field2 = h.Code },
    (s, h) => s);

But you only really have to name the second property of the anonymous class since it will take the name Soc for the first in both cases.

LINQ Join On Multiple Columns With Different Data Types

Why don't you use .ToString() as you mentioned:

var queryResult = 
from o in T1 
join p in T2 
   on new {ID = T1.ID, Weird = T1.Weird} equals new {ID = T2.ID, Weird = T2.Weird.ToString()}

That should do the trick.

linq to sql: join multiple columns from the same table

You can put your query inside a Where clause instead of using the join operator.

The join operator supports multiple clauses in VB.NET, but not C#.

Alternatively, you can use the ANSI-82 style of 'SQL' syntax, e.g.:

from t1 in table1
from t2 in table1
where t1.x == t2.x
&& t1.y == t2.y

C# Linq Join 2 tables on multiple columns and GROUP BY for count

Your query could be rewritten in Linq like this

var join1 =  from m in context.asset
         join o in context.organization
         on new {hqID = a.hq_org_id, commandID = a.command_org_id, regionID = a.region_org_id, installationID = a.installation_org_id, siteID = a.site_org_id}
         equals new {hqID = o.hq_id, commandID = o.command_id, regionID = o.region_id, installationID = o.installation_id, siteID = o.site_id}
         group new {m,o} by new {   o.org_hq_name,
                                    o.org_command_name,
                                    o.org_region_name,
                                    o.org_installation_name,
                                    o.org_site_name,
                                    o.org_subsite_name,
                                    o.org_hq_id,
                                    o.org_command_id,
                                    o.org_region_id,
                                    o.org_installation_id,
                                    o.org_site_id
                                } into gr
         select new
         {
            org_hq_name = gr.Key.org_hq_name,
            org_command_name = gr.Key.org_command_name,
            org_region_name = gr.Key.org_region_name,
            org_installation_name = gr.Key.org_installation_name,
            org_site_name = gr.Key.org_site_name,
            org_subsite_name = gr.Key.org_subsite_name,
            org_hq_id = gr.Key.org_hq_id,
            org_command_id = gr.Key.org_command_id,
            org_region_id = gr.Key.org_region_id,
            org_installation_id = gr.Key.org_installation_id,
            org_site_id = gr.Key.org_site_id,
            Count = gr.Count()
         };

---

Related Topics