cast datetimeoffet to date then check if in list of dates in linq/c#

[ghost]

This is not an issue, more of an documentation i'm not sure where to find.

i have a table with datetimeoffset column. i want to query
where

@start <= MyDateTimeOffsetCol AND MyDateTimeOffsetCol < @end
CAST(MyDateTimeOffsetCol as date) IN (@d0, @d1, ...)

Questions:

1. How is this done in EF Core?
2. should I just use raw sql?
3. should I concatenate OR clause? how is this done in EF core for scoped OR inside AND @start <= MyDateTimeOffsetCol AND MyDateTimeOffsetCol < @end AND (MyDateTimeOffsetCol=@d0 OR MyDateTimeOffsetCol=@d1 OR ...)
4. just out of curiosity is this good practice if the count of the content of the list of dates d0, d1,... is about 30 to 300? assuming Time is indexed

I tried

Steps to reproduce

.Where(o => o.Time.Date == cSharpDateTime)

works

but

List<DateTime> dates = ...
.Where(o => 
@start <= o.Time && o.Time < @end &&
dates.Contains(o.Time.Date))

didn't get converted to sql

List<DateTime> dates = ...
.Where(o => 
@start <= o.Time && o.Time < @end
dates.Contains(o.Time.Date))

Further technical details

EF Core version: "Microsoft.EntityFrameworkCore.SqlServer": "2.2.0"
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: VS Code 1.32.3

[roji]

Confirmed that SQL gets properly generated as of a2ce460 (near preview7).

Not committing QueryBugsTest repro as it doesn't seem necessary, but here is the code used:

#region Bug15208

[ConditionalFact]
public virtual void Bug_name()
{
    using (CreateDatabase15208())
    {
        using (var context = new MyContext15208(_options))
        {
            var (start, end) = (default(DateTimeOffset), default(DateTimeOffset));
            var dates = new List<DateTimeOffset> { DateTimeOffset.Now };
            var test = context.Blogs
                .Where(b =>
                    start <= b.PublicationDate.Date &&
                    b.PublicationDate < end &&
                    dates.Contains(b.PublicationDate))
                .ToList();
        }
    }
}

private SqlServerTestStore CreateDatabase15208()
    => CreateTestStore(
        () => new MyContext15208(_options),
        context =>
        {

            ClearLog();
        });

public class MyContext15208 : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    public MyContext15208(DbContextOptions options) : base(options) {}

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

public class Blog
{
    public int Id { get; set; }
    public DateTimeOffset PublicationDate { get; set; }
}

#endregion Bug15208

SQL:

SELECT [b].[Id], [b].[PublicationDate]
FROM [Blogs] AS [b]
WHERE ((@__start_0 <= CAST(CONVERT(date, [b].[PublicationDate]) AS datetimeoffset)) AND ([b].[PublicationDate] < @__end_1)) AND [b].[PublicationDate] IN ('2019-06-28T18:56:51.5342510+02:00')

[smitpatel]

Do we have regression test?

[roji]

As I wrote above it didn't seem necessary for this simple case (test posted above). But if we want a test for every bug, I'll submit a PR, no problem.

[smitpatel]

We should only close without adding test if we have existing test which covers it.