Linq Union: how to add a literal value to a query?

Question

I need to add a literal value to the query. My attempt

var aa = new List<long>(); aa.Add(0); var a = Products.Select(p => p.sku).Distinct().Union(aa); a.ToList().Dump(); // LinqPad way of showing the values 

In the above example, I get an error:

 "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator." 

If I use Entity Framework 4, for example, what can I add to the Union statement to always include the identifier "seed"?

I am trying to create SQL code as follows:

 select distinct ID from product union select 0 as ID 

Thus, I can join list itself so that I can find all the values ​​where the next highest value is missing (search for the lowest available identifier in the set).

Edit: original Linq query to find the most affordable ID

 var skuQuery = Context.Products .Where(p => p.sku > skuSeedStart && p.sku < skuSeedEnd) .Select(p => p.sku).Distinct(); var lowestSkuAvailableList = (from p1 in skuQuery from p2 in skuQuery.Where(a => a == p1 + 1).DefaultIfEmpty() where p2 == 0 // zero is default for long where it would be null select p1).ToList(); var Answer = (lowestSkuAvailableList.Count == 0 ? skuSeedStart : lowestSkuAvailableList.Min()) + 1; 

This code creates two sets of SKUs offset by one, then selects SKUs where the next maximum does not exist. After that, he chooses the minimum of this (the lowest SKU, where the next maximum is available).

For this to work, the seed must be in a kit connected together.

Answer 1

Your problem is that your query is completely converted to a LINQ-to-SQL query when you need a LINQ-to-SQL query with local manipulations on top of it.

The solution is to tell the compiler that you want to use LINQ-to-Objects after processing the request (in other words, change the resolution of the extension method to look at IEnumerable<T> , not IQueryable<T> ). The easiest way to do this is to attach AsEnumerable() to the end of your request, for example:

 var aa = new List<long>(); aa.Add(0); var a = Products.Select(p => p.sku).Distinct().AsEnumerable().Union(aa); a.ToList().Dump(); // LinqPad way of showing the values 

Answer 2

Upstairs: not answering exactly the question you asked, but solving your problem differently.

How about this:

 var a = Products.Select(p => p.sku).Distinct().ToList(); a.Add(0); a.Dump(); // LinqPad way of showing the values 

Answer 3

You should create a database table for storing constant values ​​and transfer the query from this table to the Union operator.

For example, imagine a “Default” table with the “Name” and “Value” fields with only one record (“SKU”, 0).

Then you can rewrite your expression as follows:

  var zero = context.Defaults.Where(_=>_.Name == "SKU").Select(_=>_.Value); var result = context.Products.Select(p => p.sku).Distinct().Union(zero).ToList();