How can I resolve the EF Core expression tree error when concatenating strings in my LINQ query?

The error occurs because the LINQ provider (EF Core) is trying to translate your string operations (like string.Format, Replace, and TrimEnd) into SQL. Since these methods involve handling .NET types (especially ref structs), they can’t be translated into SQL and cause the expression tree error.

To resolve the issue, you can materialize the query results from the database first (i.e. switch from SQL to in‑memory evaluation) and then do the string manipulation. One common approach is to call AsEnumerable() before the projection using string.Format. For example:

Example

CSHARPCopied
public async Task<InvoiceLocationDTO> GetLocationDetails(int locId)
{
    try
    {
        // Fetch the raw data from the database
        var rawLocation = await (
            from loc in context.Location.Where(loc => loc.Id == locId)
            .Include(x => x.LocationAddress)
            select new
            {
                loc.LocationAddress.Address,
                loc.LocationAddress.AddressExtraLine,
                loc.LocationAddress.City,
                StateName = loc.LocationAddress.State.Name,
                loc.LocationAddress.Zip
            })
            .FirstOrDefaultAsync();

        if (rawLocation == null)
        {
            return null;
        }

        // Now perform string operations in memory
        var billToAddress = string.Format(
            "{0}, {1}, {2}, {3}, {4}",
            rawLocation.Address,
            rawLocation.AddressExtraLine,
            rawLocation.City,
            rawLocation.StateName,
            rawLocation.Zip)
            .Replace(" ,", string.Empty)
            .TrimEnd(',');

        // Map the combined values to your DTO
        var locationDetails = new InvoiceLocationDTO
        {
            BillToAddress = billToAddress
            // Other mappings as needed
        };

        return locationDetails;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Explanation

1. Fetch Raw Data:
   The LINQ query fetches the data you need from the database and projects it into an anonymous type. At this point, no string formatting occurs, so EF Core can translate the query without issues.

2. In-Memory Processing:
   Once the data is materialized (after FirstOrDefaultAsync returns), the string formatting and manipulation are done in memory using the .NET runtime.

3. Map to DTO:
   Finally, the formatted address is assigned to your DTO property.

This approach ensures that all operations that cannot be translated to SQL are executed on the client side, thereby avoiding the expression tree error.