LINQ to SQL error on .Join()

Question

I'm trying to query a database and join two tables. I've never used Join() this way and I'm getting an error on the second Join():

var adjustments = data.Inventory_ARCHIVEs
    .Where(i => i.Location == comboBox3.Text && 
        upcCodes.Contains(i.UPCCode) && 
        (i.AVtime.Value.Date >= dateTimePicker1.Value.Date && 
            i.AVtime.Value.Date <= dateTimePicker1.Value.AddDays(1).Date) && 
        (i.BVtime.Value.Date >= dateTimePicker1.Value.Date && 
            i.BVtime.Value.Date <= dateTimePicker1.Value.AddDays(1).Date))
    .GroupBy(i => new { i.UPCCode })
    .Select(i => new
    {
        ID = i.Max(x => x.ID),
        i.Key.UPCCode
    })
    .Join(data.Inventory_ARCHIVEs, a => a.ID, 
        b => b.ID, (a, b) => new { a, b })
    .Join(data.BQItems, x => new { x.a.UPCCode, x.b.Location }, 
        y => new { y.UPC_Code, y.Location }, (x, y) => new
    {
        ID = x.a.ID,
        UPCCode = x.a.UPCCode,
        Date = x.b.BVtime.Value.Date,
        Description = y.Description,
        BVamount = x.b.BVamount,
        AVamount = x.b.AVamount,
        Difference = x.b.AVamount - x.b.BVamount,
        AverageCost = x.b.AverageCost,
        ExtCost = (x.b.AVamount - x.b.BVamount) * x.b.AverageCost
    });

x.a.UPCCode ,x.b.Location, y.UPC_Code, andy.Location are strings.

This is the error:

The type arguments for method 'System.Linq.Enumerable.Join<TOuter,TInner,TKey,TResult> (System.Collections.Generic.IEnumerable<TOuter>, System.Collections.Generic.IEnumerable<TInner>, System.Func<TOuter,TKey>, System.Func<TInner,TKey>, System.Func<TOuter,TInner,TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

If I DO NOT include the join by "Location" column and just use "UPCCode", it works, but when I add the second join by column, I get the error

Answer 1

I suspect this is the problem - it's at least one problem:

.Join(data.BQItems, x => new { x.a.UPCCode, x.b.Location }, 
                    y => new { y.UPC_Code, y.Location },
                    ...)

You're trying to join using two different anonymous types as the key types. They've got different properties - one has UPCCode, the other has UPC_Code. You probably want:

.Join(data.BQItems, x => new { x.a.UPCCode, x.b.Location },
                    y => new { UPCCode = y.UPC_Code, y.Location },
                    ...)

Or just be more consistent with your property names so that you use UPCCode or UPC_Code everywhere, rather than a mixture.

Answer 2

You must have most care about type of data on both side of 'equals' clause, They should be of same datatype like int and int , or string and string.

Or using lambda expression the second and third parameter must be same datatype in the Join clause.