LINQ - 'Could not translate expression' with previously used and proven query condition

Question

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 is greatly appreciated.

Answer 1

The error you are getting is telling you that LinqToSql cannot translate your null check and then string.Format expression into SQL. If you look at the SQL your first query is generating (using either LinqPad or SQL Profiler), you'll see something like:

SELECT [t0].[CompanyID], [t0].[Name], 
    (CASE 
        WHEN [t1].[AddressID] IS NULL THEN @p0
        ELSE [t1].[AddressID]
     END) AS [AddressID], 
    [t1].[Address1] AS [value], 
    [t1].[City] AS [value2], 
    [t1].[Region] AS [value3], 
    [t1].[PostalCode] AS [value4], 
    [t1].[Country] AS [value5]
FROM [Company] AS [t0]
LEFT OUTER JOIN [Address] AS [t1] ON [t0].[CompanyID] = [t1].[CompanyID]
WHERE ([t1].[IsMain] IS NULL) OR ([t1].[IsMain] = 1)

For your AddressID field, you can see that it uses a CASE-WHEN to handle the condition when AddressID is null. When you add a CASE-WHEN for MainAddress, it's trying to do the same thing for that field, but there is no SQL equivalent to string.Format it can use for the ELSE clause, so it blows up.

An easy way around this problem is to use a method to format the string. By calling a private method, LinqToSql won't try to translate the string.Format to SQL, and will instead return all of the fields necessary to populate the Address object. The method can then take care of the formatting.

For example:

LINQ:

....
select new {
    comp.CompanyID,
    comp.Name,
    AddressID = (addr.AddressID == null ? -1 : addr.AddressID),
    MainAddress = FormatAddress(addr)
};

Method:

private static string FormatAddress(Address addr)
{
    return (addr == null ? "" : 
            string.Format("{0}, {1}, {2} {3} ({4})", 
                      addr.Address1, addr.City, 
                      addr.Region, addr.PostalCode, addr.Country));
}