LINQ to SQL with if statement

Question

I have a :

            var query = db.vw_web_GetAccounts; //a sql view
            if (some condition)
            {
                query.Where(...);
            }
            else
            {
                query.Where(...);
            }
            query.Select(a =>  new AccountVM
            {
                Name = a.Name,
                ....
            });

            return query.ToList();

I want to return List<AccountVM>

There is an compilation error:

Error   CS0029  Cannot implicitly convert type 'System.Data.Entity.DbSet<vw_web_GetAccounts>' to 'System.Collections.Generic.List<AccountVM>'

Without the if condition example: It works as expected

var query = db.vw_web_GetAccounts
            .Where(...)
            .Select(a => new AccountVM
            {
                Name = a.Name,
                ....
            });

            return query.ToList();

How do I make it work with if condition?

Possible duplicate of [Linq: adding conditions to the where clause conditionally](https://stackoverflow.com/questions/10884651/linq-adding-conditions-to-the-where-clause-conditionally) — Heretic Monkey, Sep 17 '19 at 18:35

score 3 · Accepted Answer · answered Sep 17 '19 at 18:33

3

You need to cast in the 1st statement. Also in the code shown you are missing the assignments.

var query = db.vw_web_GetAccounts as IQueryable<vw_web_GetAccounts>;
if (some condition)
{
    query = query.Where(...);
}
else
{
    query = query.Where(...);
}

return query.Select(a =>  new AccountVM
{
    Name = a.Name,
    ....
}).ToList();

answered Sep 17 '19 at 18:33

Igor

60,821
10
100
175

yea. Where doesn't change the state, just returns it. i think that would work too. just be carefully because each where is a filter. so you're always making Where in a query that was already filtered. – carlosza Sep 17 '19 at 18:37
When I modify the first line I still get the same compilation error. Anything else missing? – Ben Sep 17 '19 at 18:48
@Ben - See also the return statement I posted. There is no attempt to reassign the result from `query.Select(...` to `query`. – Igor Sep 17 '19 at 18:52
@Ben - also make sure the generic type constraints are correct, I guessed based on your error message. – Igor Sep 17 '19 at 18:52
Igor, I made an update, error is gone, but now it seems the where condition is not picking up, it goes into the if condition but the result when I view contains all records. – Ben Sep 17 '19 at 19:01
@Ben - Make sure you are assigning the result of the `Where`. `query = query.Where(expression);` – Igor Sep 17 '19 at 19:03
Thank you very much!!! Works as expected. For my understanding can you please explain why we need to do cast (1 st line) and when? because if i put all into one condition without doing the if condition I dont need to do the cast. – Ben Sep 17 '19 at 19:20
1

@Ben - `db.vw_web_GetAccounts` is of type `DbSet` and `Where()` returns type `IQueryable`. You can't assign `IQueryable` to `DbSet` but the inverse is true as `DbSet` implements `IQueryable`. – Igor Sep 17 '19 at 19:30

Rashid Ali · Answer 2 · 2019-09-17T19:15:53.020

Error you are getting have nothing to do with the if condition, but the problem is that method return type is List<AccountVM> but the return value is of type DbSet<vw_web_GetAccounts>

One line solution would be the ternary operator, include condition in the Where clause and rest would be the same as you did in the without if condition version:

var query = db.vw_web_GetAccounts.Where(a => condition ? (...) : (...)).Select(a =>  
            new AccountVM
            {
              Name = a.Name,
              ....
            });

return query.ToList();

LINQ to SQL with if statement

2 Answers2