I'm trying to mimic this SQL query for which I'm getting the expected results:
In Linq query, but I'm getting some errors. This is my Linq translation:
var customerAircraftVM = await (
from ca in _context.CustomerAircrafts
join ac in _context.Aircrafts on ca.AircraftId equals ac.AircraftId
into leftJoinCustomerAircrafts
from b in leftJoinCustomerAircrafts.DefaultIfEmpty()
join aprice in (from ap in _context.AircraftPrices
join pt in _context.PricingTemplate
on ap.PriceTemplateId equals pt.Oid
where pt.Fboid == fboId
select new
{
CustomerAircraftId = ap.CustomerAircraftId,
Name = pt.Name
})
on ca.Oid equals aprice.CustomerAircraftId into pricesgpt
from a in pricesgpt.DefaultIfEmpty()
where ca.GroupId.GetValueOrDefault() == groupId && ca.CustomerId == customerId
select new CustomerAircraftsGridViewModel
{
Oid = ca.Oid,
GroupId = ca.GroupId.GetValueOrDefault(),
AircraftId = ca.AircraftId,
PricingTemplateName = p == null ? "" : p.Name,
});
I'm getting error
Object reference not set to an instance of an object.
What am I doing wrong?
You are left join results from a inner join. I am thinking that is missed in your c# code. But that is not the case for exception.
you may try like this.
var result =
from ca in _dbcontext.CustomerAircrafts
//// here join the inner join
join pta in (from f in _dbcontext.AircraftPrices
join pt in _dbcontext.PricingTemplate on ap.PriceTemplateId equals pt.Oid
where pt.Fboid == fboId
select new { pt, ap.CustomerAircraftId })
on ca.Oid equals pta.CustomerAircraftId into fcmt
where ca.GroupId == groupid
from m in fcmt.DefaultIfEmpty() //// this will do the left join
select new
{
// what you want
};