CODEWITHSUNDEEP
c#sqlentity-frameworklinqlinq-to-sql
Bill
Bill

Reputation: 1195

How and I introduce an "or" operator into a linq query join

What is the syntax introduce an "or" operator into a linq query join. It doesn't seem to like "||", "or"? I need to join one foreign key against two possible keys from the users table.

var thirdQuery = (from u in tbl_users
join ua in tbl_userDocuments on (u.TransportUserID equals ua.fkDocumentID
                             || u.WorkUserID equals ua.fkDocumentID) into uaGroup
from uaTrans in uaGroup.DefaultIfEmpty()
join ul in tbl_user_logins on uaTrans.fkUserID equals ul.user_login_id_pk into ulGroup

Without the or portion, this works but missing the extra key

var thirdQuery = (from u in tbl_users
join ua in tbl_userDocuments on (u.TransportUserID equals ua.fkDocumentID) into uaGroup
from uaTrans in uaGroup.DefaultIfEmpty()
join ul in tbl_user_logins on uaTrans.fkUserID equals ul.user_login_id_pk into ulGroup

Upvotes: 3

Views: 207

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

So you have a database with at least two tables: Users and UserDocuments. Every UserDocument has a property fkDocumentId.

Although you didn't say so, it seems to me that this is a foreign key to elements in table Users. Apparently this foreign key refers sometimes to User.TransportUserId and sometimes to User.WorkUserId.

Are you sure you want this? If fkDocumentId has a value 10, does it refer to the User with a TransportId equal to 10, or to the user with WorkUserId equal to 10, or both?

Anyway, if you investigate Enumerable.Join, then you will find that you provide two keySelectors: one to select a key from Users, and one to select a key from UserDocuments. When these two keys are equal, then the ResultSelector parameter is used to construct your joined element.

The problem is in the words "are equal". You'll have to make sure that you select your keys and provide an IEqualityComparer such that the keys will be considered equal.

Another more easy method would be to create a new Join extension method.

IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult> Join(
    this IEnumerable<TSource> outer,
    IEnumerable<TInner> inner,
    Func<TOuter, TKey> outerKeySelector1,
    Func<TOuter, TKey> outerKeySelector2,
    Func<TInner, TKey> innerKeySelector,
    Func<TOuter, TInner, TResult> resultSelector)
{
    // make two lookup tables from Outer: one for key1 and one for key2:
    var lookup1 = outer.ToLookup( outerElement => outerKeySelector1(outerElement));
    var lookup2 = outer.ToLookup( outerElement => outerKeySelector2(outerElement));

    // so if you have a TKey from the innerKeySelector, you can find if it matches key1 or key2
    foreach (TInner innerElement in inner)
    {
        TKey innerKey = innerKeySelector(innerElement)
        var matchingElementsKey1 = lookup1[innerKey];
        var matchineElementsKey2 = lookup2[innerKey];
        var allmatchingElements = matchingElementsKey1.Concat(matchingElementsKey2);

        foreach(var matchingElement in allMatchingElements)
        {
            TResult result = resultSelector(matchingElement, innerElement);
            yield return result;
        }
    }
}

Usage:

var result = tblUsers.Join(tblUserDocuments,
    user => user.TransportUserId,           // select outerKey1
    user => user.WorkUserId,                // select outerKey2
    document => document.fkDocumentId,      // select innerKey

    // when inner key matches either outerKey1, or outerKey2, create one new object:
    (user, document) => new
    {
        // Select the user documents that you want:
        UserId = user.Id,
        Name = user.Name,
        ...

        // Select the document properties that you want:
        DocumentId = document.Id,
        Author = document.Author,
        PublishedDate = document.Date,
        ...
    })

Upvotes: 1

Related Questions