I am fairly new to LINQ and can't get my head around some inconsistency in behaviour. Any knowledgeable input would be much appreciated. I see similar issues on SO and elsewhere but they don't seem to help.
I have a very simple setup - a company table and an addresses table. Each company can have 0 or more addresses, and if 0 one must be specified as the main address. I'm trying to handle the cases where there are 0 addresses, using an outer join and altering the select statement accordingly.
Please note I'm currently binding the output straight to a GridView so I would like to keep all processing within the query.
The following DOES work
IQueryable query =
from comp in context.Companies
join addr in context.Addresses on comp.CompanyID equals addr.CompanyID into outer // outer join companies to addresses table to include companies with no address
from addr in outer.DefaultIfEmpty()
where (addr.IsMain == null ? true : addr.IsMain) == true // if a company has no address ensure it is not ruled out by the IsMain condition - default to true if null
select new {
comp.CompanyID,
comp.Name,
AddressID = (addr.AddressID == null ? -1 : addr.AddressID), // use -1 to represent a company that has no addresses
MainAddress = String.Format("{0}, {1}, {2} {3} ({4})", addr.Address1, addr.City, addr.Region, addr.PostalCode, addr.Country)
};
but this displays an empty address in the GridView as ", , ()"
So I updated the MainAddress field to be
MainAddress = (addr.AddressID == null ? "" : String.Format("{0}, {1}, {2} {3} ({4})", addr.Address1, addr.City, addr.Region, addr.PostalCode, addr.Country))
and now I'm getting the Could not translate expression error and a bunch of spewey auto-generated code in the error which means very little to me.
The condition I added to MainAddress is no different to the working condition on AddressID, so can anybody tell me what's going on here?
Any help greatly appreciated.
