CODEWITHSUNDEEP
c#entity-frameworklinqdatetime
Ankit
Ankit

Reputation: 6654

LINQ to SQL check for Datetime is null or less then current date

I need to make LINQ query that will run SQL query 

    Select * from Employee where Employee.StartDate is null OR 
(Employee.StartDate is not null AND Employee.StartDate > GetDate())

I tried following code 

    Employee.Where(e => e.StartDate == null || 
(e.StartDate != null && e.StartDate > Datetime.Today);

    Employee.Where(e => e.StartDate.HasValue == false || 
(e.StartDate.HasValue != false && e.StartDate > Datetime.Today);

    Employee.Where(e => e.StartDate..Equals((DateTime?)null) || 
(e.StartDate.HasValue != false && e.StartDate > Datetime.Today);

but it does not generated the correct SQL to check both.

Upvotes: 0

Views: 5653

Answers (3)

Gert Arnold
Gert Arnold

Reputation: 109109

I can't seem to tell what I mean in comments. It's almost certain that the property StartDate is mapped as required. This can either be done by data annotations...

[Required]
public DateTime? StartDate { get; set; }

...or by fluent mapping, for example in OnModelCreating...

modelBuilder.Entity<Employee>()
    .Property(e => e.StartDate).IsRequired();

Only under these circumstances will EF ignore a (not-)null check in a LINQ expression, because for all it knows the property can't be null.

Of course it doesn't make much sense to mark a nullable property as required, so you should either make it not nullable or not required.

Upvotes: 2

Clinton Okorie
Clinton Okorie

Reputation: 33

var NewGroupEmployees = dbContext.Employees
.Where(employee => employee.StartDate == null
                || employee.StartDate > DateTime.Today).ToList();

This should return a list with the desired result.

Another option you could try is creating a stored procedure in SQL and call it from your application.

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

var thresholdDate = GetDate();
var employeesWithNewOrNoStartDate = dbContext.Employees
    .Where(employee => employee.StartDate == null
                    || employee.StartDate > thresholdDate);

In words:

from the sequence of all Employees, take only those Employees that have no StartDate at all, OR that have a fairly new StartDate

Upvotes: 1

Related Questions