FirstOrDefault() in a LINQ left-join-CodePudding

I'm trying to setup a left-join query where I'm only pulling the FirstOrDefault record from the orders table, but it's not working the way I have it, is this even possible using LINQ?

    var customerOrder = (from customer in customers
        join orderJoin in orders.FirstOrDefault(x => x.CustomerId == customer.CustomerId) on customer.CustomerId equals orderJoin.CustomerId
        join shippingJoin in shipping on customer.CustomerId equals shippingJoin.CustomerId into shippingGroup
        from shipping in shippingGroup.DefaultIfEmpty()
        select new
        {
            Customer.CustomerId,
            Order = orderJoin,
            Shipping = shipping
        }).ToList();

CodePudding user response：

make all your joins left joins and check for nullable values

var query = from company in companies
                        join product in products on company.CompanyId equals product.CompanyId into productleftjoin
                        from product in productleftjoin.DefaultIfEmpty()
                        join transaction in transactions on product.ProductId equals transaction.ProductId into transactionleftjoin
                        from transaction in transactionleftjoin.DefaultIfEmpty()
                        where transaction?.Cost * transaction?.Quantity>0
                        select new { company.CompanyName,productName=product.Name, extendPrice=transaction?.Cost* transaction?.Quantity };

CodePudding user response：

You cannot use FirstOrDefaut as source of the query. There is antoher technnique with Take(1).DefaultIfEmpty(). Note that I have added default OrderBy because databse do not guarattee records order in such case and probably DateCreated or something like that should be used.

var customerOrder = (
    from customer in customers
    from orderJoin in orders
        .Where(x => x.CustomerId == customer.CustomerId)
        .OrderByDescending(x => x.Id)
        .Take(1)
        .DefaultIfEmpty()
    join shippingJoin in shipping on customer.CustomerId equals shippingJoin.CustomerId into shippingGroup
    from shipping in shippingGroup.DefaultIfEmpty()
    select new
    {
        Customer.CustomerId,
        Order = orderJoin,
        Shipping = shipping
    }).ToList();