Linq left join with multiple columns and default values

Question

I am fairly new to linq and I need to join two tables with the following requirements:

Should left join t1 and t2.

If t2 is empty then the query should not fail - should use default values.

My query:

var final = from t1 in saDist.AsEnumerable()
            from t2 in sapGrouped.AsEnumerable()
            where
                t1.Supplier.Id == t2.Supplier.Id && t1.VatRate == t2.VatRate
            select
                new
                {
                    t1.Supplier,
                    Amount = t1.Amount - t2.Amount,
                    Advance = t1.Advance - t2.Advance,
                    Balance = t1.Balance - t2.Balance,
                    t1.VatRate
                };

Can someone correct this?

See [this](http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins)... — , May 23 '12 at 15:19

score 2 · Answer 1 · answered May 23 '12 at 16:17

This works in Linqpad as a C# program.

Basically your join syntax needed tweaking (see this), and you needed to take into account when there was nothing to join to for "t2" (so we do a null check and use 0 when null, otherwise t2.Amount, etc)

I created some dummy data so you can play around.

See http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/ for another example.

I hope it does what you want it to do.

Thanks, Dominique

    public class A
    {
        void Main()
        {

            Distributor dist1 = new Distributor() { SupplierID = 1, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "A", DeptSupplierID = 1 };
            Distributor dist2 = new Distributor() { SupplierID = 2, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "B", DeptSupplierID = 1 };
            Distributor dist3 = new Distributor() { SupplierID = 3, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "C", DeptSupplierID = 1 };
            Distributor dist4 = new Distributor() { SupplierID = 4, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "D", DeptSupplierID = 2 };
            Distributor dist5 = new Distributor() { SupplierID = 5, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "E", DeptSupplierID = 2 };
            Distributor dist6 = new Distributor() { SupplierID = 6, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "F", DeptSupplierID = 2 };
            Distributor dist7 = new Distributor() { SupplierID = 7, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "G", DeptSupplierID = 6 };
            Distributor dist8 = new Distributor() { SupplierID = 8, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "H", DeptSupplierID = 3 };
            Distributor dist9 = new Distributor() { SupplierID = 9, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "I", DeptSupplierID = 3 };
            Distributor dist10 = new Distributor() { SupplierID = 10, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "J", DeptSupplierID = 7 };
            Distributor dist11 = new Distributor() { SupplierID = 11, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "K", DeptSupplierID = 7 };
            Distributor dist12 = new Distributor() { SupplierID = 12, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "L", DeptSupplierID = 5 };

            SAPGroup Dept1 = new SAPGroup() { SupplierID = 1, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "Development" };
            SAPGroup Dept2 = new SAPGroup() { SupplierID = 2, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "Testing" };
            SAPGroup Dept3 = new SAPGroup() { SupplierID = 3, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "Marketing" };
            SAPGroup Dept4 = new SAPGroup() { SupplierID = 4, Amount = 3, Balance = 4, Advance = 3, VatRateID = 1, Name = "Support" };

            List ListOfDistributors = new List();
            ListOfDistributors.AddRange((new Distributor[] { dist1, dist2, dist3, dist4, dist5, dist6, dist7,
    dist8, dist9, dist10, dist11, dist12 }));

            List ListOfSAPGroup = new List();
            ListOfSAPGroup.AddRange(new SAPGroup[] { Dept1, Dept2, Dept3, Dept4 });

            var final = from t1 in ListOfDistributors
                        join t2 in ListOfSAPGroup
                        on new { t1.SupplierID, t1.VatRateID } equals new { t2.SupplierID, t2.VatRateID }
                        into JoinedDistAndGrouped
                        from t2 in JoinedDistAndGrouped.DefaultIfEmpty()
                        select new
                        {
                            Name1 = t1.Name,
                            Name2 = (t2 == null) ? "no name" : t2.Name,
                            SupplierID = t1.SupplierID,
                            Amount = t1.Amount - (t2 == null ? 0 : t2.Amount),
                            Advance = t1.Advance - (t2 == null ? 0 : t2.Advance),
                            Balance = t1.Advance - (t2 == null ? 0 : t2.Balance),
                            VatRateID = t1.VatRateID
                        };

            final.Dump();
        }
    }


    class Distributor
    {
        public string Name { get; set; }
        public int SupplierID { get; set; }
        public int VatRateID { get; set; }
        public int DeptSupplierID { get; set; }
        public int Amount { get; set; }
        public int Advance { get; set; }
        public int Balance { get; set; }
    }

    class SAPGroup
    {
        public int SupplierID { get; set; }
        public int VatRateID { get; set; }
        public string Name { get; set; }
        public int Amount { get; set; }
        public int Advance { get; set; }
        public int Balance { get; set; }
    }

    public class Result
    {
        public string Name1 { get; set; }
        public string Name2 { get; set; }
        public int SupplierID { get; set; }
        public int Amount { get; set; }
        public int Advance { get; set; }
        public int Balance { get; set; }
        public int VatRateID { get; set; }
    }

score 2 · Accepted Answer · answered May 24 '12 at 08:42

Thanks for your input. None of the answers did quite what I wanted, but I managed to get my original code working:

var final = from t2 in saDist.AsEnumerable()
            from t1 in sapGrouped.AsEnumerable().DefaultIfEmpty()
            where
                t1 == null || (t2.Supplier.Id == t1.Supplier.Id && t2.VatRate == t1.VatRate)
            select
                new
                {
                    t2.Supplier,
                    Amount = t2.Amount - (t1 == null ? 0 : t1.Amount),
                    Advance = t2.Advance - (t1 == null ? 0 : t1.Advance),
                    Balance = t2.Balance - (t1 == null ? 0 : t1.Balance),
                    t2.VatRate
                };

If you have any comments or improvements on this let me know, thanks.

score 0 · Answer 3 · answered May 23 '12 at 15:36

According to this, you are looking for something like (this is untested, but hopefully leads you on the right track):

var final = from t1 in saDist.AsEnumerable()
            join t2 in sapGrouped.AsEnumerable()
            on t1.Supplier.Id equals t2.Supplier.Id 
            and t1.VatRate equals t2.VatRate into t1_t2 //not sure about this line
            from t2 in t1_t2.DefaultIfEmpty()
            {
                t1.Supplier,
                Amount = t1.Amount - t2.Amount,
                Advance = t1.Advance - t2.Advance,
                Balance = t1.Balance - t2.Balance,
                t1.VatRate
            };

Notice the .DefaultIfEmpty(), this satisfies: "If t2 is empty then the query should not fail - should use default values."

[Another example](http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/) that may help you out. — , May 23 '12 at 15:39

Linq left join with multiple columns and default values

3 Answers3