Simple conditional LINQ query in Entity Framework 4

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 d开发者_C百科oing things in EF 4.0.

Thanks for your help guys,

Graham

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

1. Doing a simple null test.
2. 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.

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.

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.

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

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.

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
                );

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?