CODEWITHSUNDEEP
asp.net-mvclinqentity-frameworklambda
Lee
Lee

Reputation: 3969

LINQ Join with where condition

I can use LINQ's Join with Lambda notations no problem, but I can't work out how one would then add a where condition.

var q = query.Join(context.CustomerIds,
    x => x.CustomerId,
    y => y.CustomerId,
    (x, y) => new CustomerLookupResult()
    {
        dob = x.DateOfBirth.ToString(),
        forenames = x.Forenames,
        surname = x.Surname,
        loyaltyNo = y.Identifier,
        customerId = x.CustomerId
    });

The table I'm joining the first to contains the loyaltyNo in its Identifier column, but also contains other information in the same column and so uses a second column IdentifierTypeCode to allow filtering.

So how do I now add .Where(x => x.IdentifierTypeCode == "LOYALTY") like I would in SQL. Appending this to end refers to the new object.

Upvotes: 1

Views: 3614

Answers (3)

Muddassar Hayat
Muddassar Hayat

Reputation: 17

You can also use this way to achieve that using Linq.

var match = from t1 in context.orders
                    join t2 in context.orderdetails on
                           new { t1.OrderID } equals
                           new { t2.OrderID }
                    join t3 in context.products on
                           new { t2.ProductID } equals
                           new { t3.ProductID }
                    where t3.ProductID == id
                    select t3;
        return match.ToList();

Upvotes: 2

fdomn-m
fdomn-m

Reputation: 28621

The first parameter to the Join takes any IEnumerable, so you can apply the Where at that point, or earlier

var q = query.Join(context.CustomerIds.Where(x=>x.IdentifierTypeCode=="LOYALTY"),
    x => x.CustomerId,
    y => y.CustomerId,
    (x, y) => new CustomerLookupResult()
    {
        dob = x.DateOfBirth.ToString(),
        forenames = x.Forenames,
        surname = x.Surname,
        loyaltyNo = y.Identifier,
       customerId = x.CustomerId
    });

alternatively, if you don't like to put too much on one line:

var filteredLoyalties = context.CustomerIds.Where(x=>x.IdentifierTypeCode=="LOYALTY");
var q = query.Join(filteredLoyalties,
    ...

Upvotes: 1

Michael Minton
Michael Minton

Reputation: 4495

You could apply your Where before doing the join.

var q = customerLoyalties
        .Where(x => x.IdentifierTypeCode == "LOYALTY")
        .Join(customers,
              x => x.CustomerId,
              y => y.CustomerId,
              (x, y) => new CustomerLookupResult()
              {
                CustomerId = y.CustomerId,
                Name = y.Name,
                IdentifierTypeCode = x.IdentifierTypeCode
              });

Upvotes: 2

Related Questions