CODEWITHSUNDEEP
c#linqiqueryable
BILL
BILL

Reputation: 4869

Union of the results with an empty LINQ query

In the database, I have some of the products and prices. I need to search for the price limits. The problem is that there may be several limits. For example, 0 - 500 $ 1500 - $ 2000

How to merge this LINQ queries

var products = from product in db.Products
where product.Price >= 0 and product.Price <= 500

var products = from product in db.Products
where product.Price >= 1500 and product.Price <= 2000

My varriant:

  IQueryable<Deal> allResults = null;
            for(var i = 0; i < price.Length - 1; i = i + 2)
             {
              decimal start,end;
              decimal.TryParse(price[i], out start);
              decimal.TryParse(price[i+1], out end);

              var tempResults = from product in query
                         where (product.DiscountPrice >= start && product.DiscountPrice <=                    end)
                       select product;
                    tempResults.Union(allResults);
                }

how to create an empty query to join ?

Upvotes: 1

Views: 4667

Answers (2)

CodesInChaos
CodesInChaos

Reputation: 108840

One way is to merge the conditions:

var products = from product in db.Products
where (product.Price >= 0 and product.Price <= 500)||product.Price >= 1500 and product.Price <= 2000

Another is to use Union or Concat:

var products = (from product in db.Products
                     where product.Price >= 0 and product.Price <= 500)
         .Union(from product in db.Products
                     where product.Price >= 1500 and product.Price <= 2000);

Adapting your code on can do:

IQueryable<Deal> allResults = null;
for(var i = 0; i < price.Length - 1; i = i + 2)
{
    decimal start,end;
    decimal.TryParse(price[i], out start);
    decimal.TryParse(price[i+1], out end);

    var tempResults = from product in db.Products
                      where (product.DiscountPrice >= start && product.DiscountPrice <= end)
                      select product;
    if(allResults!=null)
       allResults=allResults.Union(tempResults);
    else
       allResults=tempResults;
}

You don't need an empty query in to merge with, you can just add a special case that tests if this is the first query.

But I don't like that code much, because it mixes two concerns: The construction of the queries, and the merging of the queries. So I'd split it in two:

IEnumerable<Tuple<Decimal,Decimal>> ParsePrices(string[] rawPrices)
{
    for(var i = 0; i < price.Length - 1; i = i + 2)
    {
       decimal start=decimal.Parse(price[i]);
       decimal end  =decimal.Parse(price[i+1]);

       yield return Tuple.Create(start, end);
}

var prices=ParsePrices(rawPrices);
IEnumerable<IQueryable<Deal>> partialQueries=prices.Select( interval=>
       from product in db.Products
       where (product.DiscountPrice >= interval.Item1 && product.DiscountPrice <= interval.Item2)
       select product;);
var mergedQuery=partialQueries.Aggregate((q1,q2)=>q1.Union(q2));

Upvotes: 3

Douglas
Douglas

Reputation: 54897

You can create an empty query using Enumerable.Empty<T>().AsQueryable().

An issue you have in your code: Union does not alter any of the sequences that you pass to it as parameters; rather, it creates a new sequence which it returns. Thus, you should be assigning the result of your tempResults.Union(allResults) operation to allResults.

Here is some sample code (not tested):

IQueryable<Deal> filteredResults = Enumerable.Empty<Deal>().AsQueryable();

for (int i = 0; i < price.Length - 1; i += 2)
{
    decimal start, end;
    decimal.TryParse(price[i], out start);
    decimal.TryParse(price[i+1], out end);

    var tempResults = from product in db.Products
                      where product.DiscountPrice >= start && product.DiscountPrice <= end
                      select product;

    filteredResults = filteredResults.Union(tempResults);
}

Upvotes: 3

Related Questions