Transforming Enumerable into Queryable with Linq: A How-To Guide on Concat, Union, and Troubleshooting EF Core 6 Errors

When we write the linq code that use set operation (concat, union,intersect), sometimes we get many errors. After we search the error, usually we just change from queryable to enumerable. That solution is just fine, but we also must know the difference between enumerable and queryable. Enumerable would call database twice when we use the set operation, but when we use queryable we only call database once. This will impact the performance.

I have listed 3 common errors when we use set operation on linq. Each error has a different solution. I will explain it in an error-solution order.

Error

System.InvalidOperationException: Unable to translate set operation when matching columns on both sides have different store types.

Solution

From

select new 
{
  Description = x.Description
}

To

select new 
{
  Description = Convert.ToString(x.Description)
}

You should change varName to Convert.ToString(varName) on property that has different Max Length. Don't use varName.ToString() method. Why? Because Convert.ToString(varName) will be converted to CONVERT(NVarChar(MAX),table_name.column_name) on SQL Server and table_name.column_name::text on PostgreSQL.

Code Example:

	var subQueryTransactionB = (
	from md in _dbContext.MainDocuments.AsNoTracking()
	join tb in _dbContext.TransactionDocumentBs on md.Id equals tb.MainDocumentId
	select new
	{
	MainDocumentStatus = md.DocumentStatus,
	MainDocumentNumber = md.DocumentNumber,
	MainDocumentDate = md.DocumentDate,
	MainDescription = md.Description,
	DocumentNumberA = "",
	DocumentDateA = "",
	DetailsA = "",
	DocumentNumberB = Convert.ToString(tb.DocumentNumber), //solve the eror of different store types
	DocumentDateB = tb.DocumentDate.ToString(),
	DetailsB = Convert.ToString(tb.Details), //solve the eror of different store types
	}
	).AsQueryable();

view raw error1.cs hosted with ❤ by GitHub

More Info: https://github.com/dotnet/efcore/issues/19129

Error

System.InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.

Solution

From

var a = 
( 
  from x in _dbContext.TableNameA
  select new 
  {
    Date = x.Description.ToString("dd/MM/yyyy")
  }
).AsQueryable();

var b = 
( 
  from x in _dbContext.TableNameB
  select new 
  {
    Date = x.Description.ToString("dd/MM/yyyy")
  }
).AsQueryable();

var result = a.Concat(b).ToList();

To

var a = 
( 
  from x in _dbContext.TableNameA
  select new 
  {
    Date = x.DateDoc
  }
).AsQueryable();

var b = 
( 
  from x in _dbContext.TableNameB
  select new 
  {
    Date = x.DateDoc
  }
).AsQueryable();

var result = a.Concat(b)
  .Select(x=> new 
  {
    Date = x.Date.ToString("dd/MM/yyyy") 
  })
  .ToList();

You should check all interpolated string on Select call method on linq. Interpolated string will cause the error because interpolated string is client evaluation. Rewrite the interpolated string after union or concat method. Example of interpolated string

1. varName.ToString()
2. varName.ToString("dd/MM/yyyy")
3. varName1 + " " + varName2
4. $"{varName.Trim()} - {varName.Trim()}")

Code Example:

	var result = subQueryMainDocumentOnly
	.Union(subQueryTransactionA)
	.Union(subQueryTransactionB)
	.Select(x => new TransactionReportDto
	{
	//rewrite all interpolated string after set operation (concat, union, intersect)
	MainDocumentStatus = x.MainDocumentStatus.ToString(),
	MainDocumentNumber = x.MainDocumentNumber,
	MainDocumentDate = x.MainDocumentDate.ToString("dd/MM/yyyy"),
	MainDescription = x.MainDescription,
	DocumentNumberA = x.DocumentNumberA,
	DocumentDateA = x.DocumentDateA,
	DetailsA = x.DetailsA,
	DocumentNumberB = x.DocumentNumberB,
	DocumentDateB = x.DocumentDateB,
	DetailsB = x.DetailsB,
	})
	.ToList();

view raw error2.cs hosted with ❤ by GitHub

More Info: https://github.com/dotnet/efcore/issues/16243

Error

System.InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.

Solution

From

var result = a.Concat(b)
  .GroupBy(x => x.Code)
  .Select(x => new
  {
    Code = x.Key,
    ListDoc = x.ToList()
  }
  );

To

var result = a.Concat(b)
  .ToList()
  .GroupBy(x => x.Code)
  .Select(x => new
  {
    Code = x.Key,
    ListDoc = x.ToList()
  }
  );

Check any ToList() on select call method in Linq. Add ToList() method before GroupBy or Distinct call.

Code Example:

	var result = subQueryMainDocumentOnly
	.Union(subQueryTransactionA)
	.Union(subQueryTransactionB)
	.Select(x => new TransactionReportDto
	{
	MainDocumentStatus = x.MainDocumentStatus.ToString(),
	MainDocumentNumber = x.MainDocumentNumber,
	MainDocumentDate = x.MainDocumentDate.ToString("dd/MM/yyyy"),
	MainDescription = x.MainDescription,
	DocumentNumberA = x.DocumentNumberA,
	DocumentDateA = x.DocumentDateA,
	DetailsA = x.DetailsA,
	DocumentNumberB = x.DocumentNumberB,
	DocumentDateB = x.DocumentDateB,
	DetailsB = x.DetailsB,
	})
	.ToList() //add ToList() method call before the 'Groupby' or 'Distinct'
	.GroupBy(x => x.MainDocumentStatus)
	.Select(x => new TransactionReportGroupDto
	{
	MainDocumentStatus = x.Key.ToString(),
	Reports = x.ToList()
	});

view raw error3.cs hosted with ❤ by GitHub

More Info: https://github.com/dotnet/efcore/issues/16243

Play, Run, And Debug Yourself

If you want to play with this error, you can go to my github repo. I made a simple web api that you can use to understand this article deeper. Below is the screenshot of the endpoint that you can try.

After you run the server, you can open https://localhost:7080/swagger/index.html and run the /Transaction/GetReportQueryableErrorRunTime endpoint. Then, check what will happen. Happy coding! 😊😊😊