Where list contains any in List

Question

I am just sending this data to reporting Engine(SSRS) in Asp.net MVC5.
Everything is fine, but this query is taking a lot of time since I have to loop through ListProducts (I guess ListProducts is the size of the database matches).

I am just looking for a way to optimize this query.

I have tried any and contains (as seen below), but they do not seem to work in single table.

context.Products.Where(w => w.ProductDetail.Any(a => a.startDate >= startDate 
                                                     && a.endDate <= endDate))

I got this from here

2)I tried this as well

context.Products.Where(w => ListProducts.Any(x =>w.Contains(x)))

but this also does not work and generates a compile time error that

System.Guid does not contains definition of 'Contains'

Is there any other way, or i am doing it the only correct way?

foreach (var item in ListProducts)
{
    List.AddRange(_context.Products.Where(w => w.ProductId== item).Select(q => new ProductVM
    {
        Name = q.Name,
        Quantity = q.Quantity,

    }).ToList().Select(item=> new ProductVM
    {
             Name = item.Name,
        Quantity = item.Quantity,
    }).ToList());
}


public class Product
{
    public Nullable<System.Guid> ProductId { get; set; }
    public string Name { get; set;}
    public decimal Quantity { get; set; }
}

At the very least you might want to provide a definition or POCO of `Product`. It can be inferred but you're making people do more analysis work than they need to in solving what is otherwise a pretty simple problem. — nathanchere, Jun 12 '15 at 12:11
Do you have to use LINQ? What about executing Raw SQL over the `_context` so you can optimise the query. You could even use an ORM like Dapper and it would nicely map your object(s) up for you. Very powerful tool and very fast too.Would be nice to have a data structure so I can see how this all ties up in the database. — scgough, Jun 12 '15 at 12:12
Actually i have not provided the original classes and data due to some restrictions but i have made example based on those classes , The actual design of the database is quite complex but i have made a simple example for quick understanding, so you can take it as a product class having few properties like Name quantity and i just want to get data as explained in questions ..if need more explanation then feel free to ask — Dragon, Jun 12 '15 at 12:15
".if need more explanation then feel free to ask " - if you want more answers, feel free to explain more. — nathanchere, Jun 12 '15 at 12:16
Is `ListProducts` a list of `int` values? Is `List` a list of type `Product`? — scgough, Jun 12 '15 at 12:16
@scgough linq is the only way for me and i am already using entity framework as orm — Dragon, Jun 12 '15 at 12:16
Contains should work. Something like this: `context.Products.Where(p => ListProducts.Contains(p.Id));` — Niels Filter, Jun 12 '15 at 12:19
@nathanchere As i earlier said i have not provided the actual data .the poco classes of the demo data can be something like this public class Product { public string Name { get; set;} public decimal Quantity { get; set; } } — Dragon, Jun 12 '15 at 12:23
@scgough for your answer i have to made some changes in my code and after that the error got removed , so now i want to make it as answer but you have removed your answer..? — Dragon, Jun 12 '15 at 19:24

scgough · Accepted Answer · 2015-06-12T12:32:23.340

0

Ok, can you do:

var thelist = _context.Products.Where(n => ListProducts.Contains(n.ProductId)).Select(n => new ProductVM
{
     Name = n.Name,
     Quantity = n.Quantity
}).ToList();

edited Jun 12 '15 at 12:32

answered Jun 12 '15 at 12:21

scgough

5,099
3
30
48

why mix query and method format in the second example? Also query format requires a `select`. – juharr Jun 12 '15 at 12:31
edited @juharr - you're right! was thinking out loud. :) – scgough Jun 12 '15 at 12:32
the first one gives this error..Error 111 The best overloaded method match for 'System.Collections.Generic.List.Contains(System.Guid)' has some invalid arguments – Dragon Jun 12 '15 at 13:02
It might be obvious what was a Guid if you would bother taking my earlier suggestion seriously. You don't need to provide ***data*** but at least the ***data structures*** relevant to your question. – nathanchere Jun 12 '15 at 13:08
this error is due to nullable productId..now how should i deal with this – Dragon Jun 12 '15 at 13:08
@nathanchere is right - you really should provide more info or we're just stabbing in the dark. :-/ – scgough Jun 12 '15 at 13:09
so- was my answer correct (based on the lack of info available) @pro-grammed ? if so can you mark it as so please? – scgough Jun 12 '15 at 13:11
I have edited the details in my question...i am working on this and trying to implement your answer and finding the solutions of error...if it worked then i would surely mark as answer – Dragon Jun 12 '15 at 13:24
can anyone describe the downvotes given to this answer – Dragon Jun 13 '15 at 07:47
I think it was because I added another possible solution as well as the one you see but it contained syntax errors. I removed it but was down voted anyway. Thank you for marking as answer. Glad I could help. – scgough Jun 13 '15 at 07:48
This answer has removed the usage of foreach loop , so now the query performance is better than before,I will add some more details in my question so that this would be helpful for others having same problem. – Dragon Jun 13 '15 at 07:52

Where list contains any in List

1 Answers1