LINQ to SQL Join issues with FirstOrDefault()

Question

I posted a question earlier, but I've narrowed down the problems now and it's something pretty different. Basically, I have a rather long query which aims to select values from a database into a custom structure (which I'll show below), but I'm having issues with the joining - paticulary trying to only select the first row of a join. Here is my query:

IENumerable<VehicleDetails> list = (
    from s in dc.Vehicles
    join veh in dc.VehicleTypes 
        on s.VehicleType equals veh.ID into vg
    from v in vg.DefaultIfEmpty()
    join consignments in dc.Consignments
        .FirstOrDefault(x => x.TripDate > dateFrom && 
            x.TripDate < dateTo && 
            x.DeliveryDepot == depot.Letter && 
            (x.DeliveryStatus == 2 || x.DeliveryStatus == 3)) 
        on new
        {
            Reg = s.VehicleReg, 
            Depot = s.VehicleDepot 
        } equals new
        {
            Reg = consignments.VehicleReg, 
            Depot = consignments.DeliveryDepot
        } into con
    from c in con.DefaultIfEmpty()
    select new 
    {
        VehicleType = (
            s.VehicleType == null ? "?":
            v.VehicleType1.ToString()),
        TotalRate = c.Rate + c.AddCharges,
        VehicleReg = (string.IsNullOrEmpty(c.VehicleReg) ? 
            c.Subcontractor: c.VehicleReg),
        VehicleTypeName = (v.VehicleTypeDescription ==  null ? 
            "SubContractor": v.VehicleTypeDescription)
    }); 

My struct:

public struct VehicleDetails
{
     internal string VehicleType;
     internal decimal TotalRate;
     internal string VehicleReg;
     internal string VehicleTypeName;
}

With the FirstOrDefault() in the second join, I get:

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to group join.

Without it (and replacing the FirstOrDefault with a Where instead), I get an error about implicity converting an anonymoustype into a 'vehicledetials' type. Both errors occur on the from c in con.DefaultIfEmpty() call.

Any ideas would be much, much appreciated

Answer 1

FirstOrDefault() will eagerly return a single element, but what you need is a collection (IQueryable) of elements.

So

dc.Consignments
    .Where(x => x.TripDate > dateFrom && 
                x.TripDate < dateTo && 
                x.DeliveryDepot == depot.Letter && 
                (x.DeliveryStatus == 2 || x.DeliveryStatus == 3))
   .Take(1)

will return a deferred collection that will have only one element.