Geek Answers Handbook

LINQ - "Failed to translate expression" with previously used and verified query condition

I am new to LINQ and cannot plunge into any kind of inconsistency in behavior. Any knowledgeable materials would be highly appreciated. I see similar issues in SO and elsewhere, but they don't seem to help.

I have a very simple setup - a company table and an address table. Each company can have 0 or more addresses, and if> 0, you must specify the main address. I am trying to handle cases where there are 0 addresses using an external connection and changing the select statement accordingly.

Note that I am currently binding the output directly to the GridView, so I would like to keep all the processing in the request.

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)
    };

GridView ", , ()"

, MainAddress,

MainAddress = (addr.AddressID == null ? "" : String.Format("{0}, {1}, {2} {3} ({4})", addr.Address1, addr.City, addr.Region, addr.PostalCode, addr.Country))

Could not translate expression spewey , .

, MainAddress, AddressID, - , ?

.

+5
1

, , , LinqToSql , string.Format SQL. SQL, ( LinqPad SQL Profiler), - :

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)

AddressID , CASE-WHEN , AddressID null. CASE-WHEN MainAddress, , SQL- string.Format ELSE, .

. , LinqToSql string.Format SQL , Address. .

:

LINQ:

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

:

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));
}
+6

More articles:


All Articles