Simple conditional LINQ query in Entity Framework 4

Question

I have recently migrated this code to Entity Framework 4 and it is failing. Obviously if status does not have a value, return all matches, if it does have a value match ones where user.StatusID == 1.

return users.SingleOrDefault(
                user =>
                user.Username == username &&
                user.EncryptedPassword == password &&
                (!status.HasValue || user.StatusID == 1)
                );

Exception returned:

ArgumentException: The specified value is not an instance of type 'Edm.Int32'
Parameter name: value

However, removing the conditional test and it works fine:

return users.SingleOrDefault(
                user =>
                user.Username == username &&
                user.EncryptedPassword == password &&
                user.StatusID == 1
                );

Any ideas? How do you perform conditional testing in EF 4? Surely not separate if lines?

I use these conditional tests time and time again in Linq to Sql; it is really odd as to why this is not functioning in EF 4. There must be something simple going wrong, perhaps there is a recommended alternate way of doing things in EF 4.0.

Thanks for your help guys,
Graham

what the Edm.Int32? is it your own implementation of System.Int32 ? — Stecya
– Stecya, Commented Apr 26, 2011 at 11:12
@Stecya : msdn.microsoft.com/en-us/library/bb387164.aspx , seems to be some internals of EF. — Euphoric
– Euphoric, Commented Apr 26, 2011 at 11:14

GONeale · Accepted Answer · 2011-04-26 11:34:12Z

5

Ok, I solved it by a combination of two things.

Doing a simple null test.
Testing the local cast status variable without the .Value method.

Both must be in place otherwise it will continue to fail with the error! It would have been nice to test the value property, but I guess the query must be really simple - quite interesting!

return users.SingleOrDefault(
                user =>
                user.Username == username &&
                user.EncryptedPassword == password &&
                (status == null || user.StatusID == (int) status)
                );

I will wait for any better implementation otherwise accept my own answer. But thanks for everybody's help.

answered Apr 26, 2011 at 11:34

GONeale

26.5k22 gold badges112 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

greenoldman Over a year ago

EF looks better and better... Thank you for the solution, it bit me today.

Euphoric · Accepted Answer · 2011-04-26 11:19:35Z

2

How about creating separate variable for !status.HasValue and using this instead in query?

I think problem here is that EF tries to pass your status variable as parameter into the query and then do the logic in the SQL itself. Try checking what SQL is generated.

edited Apr 26, 2011 at 11:19

answered Apr 26, 2011 at 11:07

Euphoric

12.9k1 gold badge35 silver badges47 bronze badges

2 Comments

GONeale Over a year ago

Wow, that passes, declaring var test = !status.HasValue then testing test. But it's not nice. However it has led me to try every casting and bracketing trick I can think of because surely if it works like this, it can work in-line, they both resolve false. Hum. Any further thoughts?

GONeale Over a year ago

I think the problem lies in the fact status is not an int; and it's getting confused comparing one enum and one int type in the one construct. No casting can function either because it is nullable :(

Mike · Accepted Answer · 2011-04-26 11:54:05Z

2

An alternative way to doing the null check within the where clause would be to seperate out the optional parameter and only apply it if required.

e.g.

    var data = users.Where(
        user =>
            user.Username == username &&
            user.EncryptedPassword == password
        );

    if (status.HasValue)
    {
        data = data.Where(user => user.StatusID == status.Value);
    }

    return data.FirstOrDefault();

I don't think it'll offer much over your current solution other than a little more readability.

answered Apr 26, 2011 at 11:54

Mike

6715 silver badges10 bronze badges

Comments

jonchicoine · Accepted Answer · 2011-04-26 15:44:55Z

1

Seems like you've already found a solution...

However, just fyi... i have no problem with the following line in VS 2010

  Nullable<int> status = 0;
  String username = "Alexandre 2";

    var test = _context.Contacts.SingleOrDefault(c => c.FirstName == username && (!status.HasValue || c.ContactID == 1));

I get no errors and the Contact object i expect is return ... so if anything, it makes me wonder what is the type of your user.StatusID field?

best of luck

answered Apr 26, 2011 at 15:44

jonchicoine

4643 silver badges15 bronze badges

Comments

Hassan · Accepted Answer · 2011-04-26 11:02:21Z

0

I think you should enclose user.StatusID == 1 in additional set of parentheses, as I think currently EF tries to apply || operation to status.HasValue and user.StatusId and then compare the result to 1.

answered Apr 26, 2011 at 11:02

Hassan

2,6632 gold badges19 silver badges20 bronze badges

1 Comment

GONeale Over a year ago

Alas, no luck. Good thinking though.

Farzin Zaker · Accepted Answer · 2011-04-26 11:07:17Z

0

what is status.HasValue ? if status is a field of User you should call it like this :user.status.HasValue

so simply do this :

if(status.HasValue)
    return users.SingleOrDefault(
                user =>
                user.Username == username &&
                user.EncryptedPassword == password &&
                user.StatusID == 1
                );

    return users.SingleOrDefault(
                user =>
                user.Username == username &&
                user.EncryptedPassword == password
                );

edited Apr 26, 2011 at 11:07

answered Apr 26, 2011 at 11:02

Farzin Zaker

3,6043 gold badges27 silver badges35 bronze badges

2 Comments

GONeale Over a year ago

Hi Farzin, it's a nullable int, and rather a field of the enclosing method.

Farzin Zaker Over a year ago

edited, I know this is not a common solution, but works for you! ;)

Ahmed Magdy · Accepted Answer · 2011-04-26 12:07:31Z

0

At the first galance it appeare like this in my mind:

return users.SingleOrDefault(
                user =>
                user.Username == username &&
                user.EncryptedPassword == password &&
                (status == null || user.StatusID == 1)
                );

but when I look at it more i feel it's wrong so What about?

return users.SingleOrDefault(
    user =>
    user.Username == username &&
    user.EncryptedPassword == password &&
    status != null &&
    user.StatusID == 1)
  );

what is the exactly business logic behind it?

answered Apr 26, 2011 at 12:07

Ahmed Magdy

6,0888 gold badges46 silver badges77 bronze badges

1 Comment

GONeale Over a year ago

Ah, well, your second query will always enforce the requirement of a status, which is not necessarily the case. status could be null and in that case, I want to fetch all entries. That's why I use the || operator.

Collectives™ on Stack Overflow

Simple conditional LINQ query in Entity Framework 4

7 Answers 7

1 Comment

2 Comments

Comments

Comments

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

2 Comments

Comments

Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related