How to Do Joins in Linq on Multiple Fields in Single Join

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: 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 left joins in LINQ on multiple fields in single join

Your Code

from p in Person
 join pg in PersonGroup on new { p.PersonID, groupID } equals new { pg.PersonID, pg.GroupID } into t
 from rt in t.DefaultIfEmpty()

Change it to

from p in Person
 join pg in PersonGroup on new { Person = p.PersonID, Group = groupID } equals new { Person = pg.PersonID, Group = pg.GroupID } into t
 from rt in t.DefaultIfEmpty()

That way it will join using the Anonymous type

LINQ join multiple tables by same column

So you have a name, and you want all Textings that refer to a Student with this name, and all Textings that refer to a member of Staff with this Name.

My advice would be to concat the Student textings with the Staff textings. You could do that in one big LINQ statements, however this would make it quite difficult to understand. So I'll do it in two steps, then Concat it in one query:

const int student = 1;
string name = "William Shakespeare";

var studentTextings = textings.Where(texting => texting.PersonTypeId == student)
    .Join(students.Where(student => student.Name == name),

    texting => texting.PersonId,    // from every Texting take the foreign key
    student => student.Id,          // from every Student take the primary key

    // parameter resultSelector:
    // from every texting with its matching student make one new:
    (texting, studentWithThisTexting) => new
    {
        // Select the Texting properties that you plan to use
        Id = texting.Id,
        ...
    }

In words: from all Textings, keep only those Textings that refer to a student, so you know that the foreign key refers to a primary key in the table of Students. From all Students keep only those Students that have the requested name.

Join all remaining Textings and the few remaining Students that have this name on primary and matching foreign key.

Do something similar for members of Staff:

const int staff = 2;
var staffTextings = textings.Where(texting => texting.PersonTypeId == staff)
    .Join(staffMembers.Where(staffMember => staffMember.Name == name),

    texting => texting.PersonId,    // from every Texting take the foreign key
    staffMember => staffMember.Id,  // from every Staff member take the primary key

    // parameter resultSelector:
    (texting, staffMembers) => new
    {
        // Select the Texting properties that you plan to use
        Id = texting.Id,
        ...
    }

Now all you have to do is Concat these two. Be aware: you can only Concat similar items, so the resultSelector in both Joins should select objects of exactly the same type.

var textingsOfPersonsWithThisName = studentTextings.Concat(staffTextings);

There is room for improvement!

If you look closely, you'll see that the textings table will be scanned twice. The reason for this, is because your database is not normalized.

Can it be, that a Texting for a Student will ever become a Texting for a member of Staff? If not, my advice would be to make two tables: StudentTextings and StaffTextings. Apart from that queries will be faster, because you don't have to check PersonType, this also has the advantage that if later you decide that a StudentTexting differs from a StaffTexting, you can change the tables without running into problems.

If you really think that sometimes you need to change the type of a texting, and you don't want to do this by creating a new texting, you also should have two tables: one with StudentTextings, and one with StaffTextings, both tables having a one-to-one relations with a Texting.

So Students have one-to-many with StudentTextings, which have one-to-one with Textings. Similar for Staff and StaffTextings.

So Student [4] has 3 StudentTextings with Id [30], [34], [37]. Each of these StudentTextings have a foreign key StudentId with value [4]. Each StudentTexting refers to their own Texting with a foreign key: [30] refers to texting [101], so it has foreign key 101, etc.

Now if texting [101] has to become a texting for Staff [7], you'll have to delete the StudentTexting that refers to [101] and create a new StaffTexting that refers to Staff [7] and Texting [101]

By the way, since the combination [StudentId, TextingId] will be unique, table StudentTextings can use this combination as primary key. Similar for StaffTextings

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 */
}

SQL to LINQ unable to convert SQL join query with multiple conditions with and

Remarks

• Your LINQ attempt is missing the X4AlarmGroups.
• To join X4Journals and X4AlarmConfigs on two columns I used ValueTuples.
• I also unwrapped the intermediate anonymous types.

var finalAlarm = xAMRuntimeX4Context.X4Journals
    .Join(xAMControlX4Context.X4InstanceRemarks,
            j => j.InstanceID, i => i.Instance_ID, (j, i) => new { j, i })
    .Join(xAMControlX4Context.AutomationControlPropertyRemarks,
            ppc => ppc.j.PropertyID, p => p.Property_ID, (ppc, p) => new { ppc.j, ppc.i, p })
    .Join(xAMControlX4Context.X4AlarmConfigs,
            ppc2 => new { k1 = ppc2.j.InstanceID, k2 = ppc2.j.PropertyID },
               a => new { k1 = a.X4Instance_ID,   k2 = a.X4Property_ID },
            (ppc2, a) => new { ppc2.j, ppc2.i, ppc2.p, a })
    .Join(xAMControlX4Context.X4AlarmGroups,
            ppc3 => ppc3.a.X4AlarmGroup_ID, g => g.ID, (ppc3, g) => new { ppc3.j, ppc3.i, ppc3.p, ppc3.a, g })
    .Where(ppc4 => ppc4.i.Language == "iv" && ppc4.p.Language == "iv")
.OrderBy(ppc4 => ppc4.j.OnTime)
.Select(x => new {
    x.j.ID,
    AlarmAppearTime = x.j.OnTime,
    AlarmDisappearTime = x.j.OffTime,
    AlarmAckTime = x.j.OnNoticeTime,
    AlarmCloseTime = x.j.OffNoticeTime,
    AssetID = x.j.InstanceID,
    Asset = x.i.RemarkPath,
    MessageID = x.j.PropertyID,
    AlarmMessage = x.p.Remark,
    PriorityID = x.a.X4AlarmGroup_ID,
    PriorityGroup = x.g.Name
});

---

Related Topics