How to use 'Sql in' with ASP.NET MVC repository pattern

Question

I have this SQL query :

select * 
from press 
where compId = 36 
  and categories in (1, 7, 21);

I tried to apply it in format of an ASP.NET MVC repository pattern:

 iPressRepository.GetAll().Where(x => (x.compId == 36)).ToList();

But my result is incorrect, because I missed categories 'in' in my second query. Is there a way to apply IN operator with a repository pattern?

Thanks in advance

Possible duplicate of [LINQ to SQL in and not in](https://stackoverflow.com/questions/3047657/linq-to-sql-in-and-not-in) — Rafalon, Sep 14 '18 at 07:11

score 4 · Accepted Answer · answered Sep 14 '18 at 07:12

4

var categoriesToLookFor = new[] { 1, 7, 21 };
var press = iPressRepository.GetAll()
                            .Where(x => (x.compId == 36) && (categoriesToLookFor.Contains(x.categories)))
                            .ToList();

answered Sep 14 '18 at 07:12

trashr0x

6,457
2
29
39

Thanks, but my 'categoriesToLookFor' is int field, so 'int does not contain a definition for.. like that error message – Janaka Sep 14 '18 at 07:20
What do you mean "my `categoriesToLookFor`"? I thought the column in question on the database was named `categories`, based on your SQL query. – trashr0x Sep 14 '18 at 07:22
Can you please provide the entire error, and specify where it's going wrong? – trashr0x Sep 14 '18 at 07:29
It works Brother. Thanks a lot. My categories field was int?(nullable int). Then I change it to int. Then works. Thanks a lot – Janaka Sep 14 '18 at 07:32

score 0 · Answer 2 · answered Sep 14 '18 at 07:10

0

I assume that should be something like this

iPressRepository.GetAll().Where(x => (x.compId == 36)&&(x.categories==1||x.categories==7||x.categories==21)).ToList();

answered Sep 14 '18 at 07:10

maximelian1986

2,308
1
18
32

This works but my categories comes dynamically. Then this query length may vary time to time. Thank you – Janaka Sep 14 '18 at 07:36

score -1 · Answer 3 · answered Sep 14 '18 at 07:41

If you use GetAll() you will get all data from SQL but you need just some data so you should use AsQueryable()

Repository :

public IQueryable<T> AsQueryable()
{
    return _entities.AsQueryable();
}

Controller :

var list = iPressRepository.AsQueryable()
        .Where(i => (new[] { 1, 7, 21 }).Contains(i.categories) && i.compId == 36).ToList();

How to use 'Sql in' with ASP.NET MVC repository pattern

3 Answers3