CODEWITHSUNDEEP
c#linqentity-framework-4
N Rocking
N Rocking

Reputation: 3035

query generated by LINQ removes where clause

I am generating a simple query using LINQ. My model is 

public class Employee
{
    public int EmployeeId {get; set;}
    public string FirstName {get; set;}        
    [Required]
    public string LastName {get; set;}
}

My LINQ query is

var q = db.Employees.Where (i => i.LastName != null);

This query is translated into the following SQL (see there is no where clause)

SELECT 
[Extent1].[EmployeeId ] AS [EmployeeId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Employees] AS [Extent1]

If I remove the Required attribute from my model, the SQL query adds the Where clause. So, I'am assuming that LINQ optimizes the query by removing the where clause. But I want to keep the Required attribute and want to use LINQ. Is there any work around?

Since I am working on Old database, I have some records where LastName is null. But I want to add Required field so that all new records will have values for LastName.

Upvotes: 0

Views: 121

Answers (1)

D Stanley
D Stanley

Reputation: 152511

I'm assuming you're using code-first to generate your database. What seems to be happening is you're adding a IS NOT NULL condition against a DB column that is not nullable, which EF appears to be optimizing away.

If the DB column is not nullable, then having an IS NOT NULL WHERE clause makes no sense.

If your DB column is nullable, but you want the model to require a value, then you may need to add another layer to separate your domain model from your database schema. EF is meant to model your data schema as closely as possible. If you want to apply business rules that differ from your data constraints, you may need to to that in your ViewModel (or whatever is appropriate for your architecture).

Upvotes: 2

Related Questions