Compare date with today on the server side with EntityFramework Core and linq to entities

asp.net-core entity-framework-core

Question

I'm trying to translate some code from EF6 to EF Core 1 RC1 and I'm stuck at the point on wich I need to compare the dates with the current one, on the server side using linq. There is also another part of the query wich was getting the day of week of these dates. The EF6 code uses the SqlFunctions class wich is fully qualified as System.Data.Entity.SqlServer.SqlFunctions, that afaik hasn't been ported to the new version. What's the recommended approach or the alternative to keep this working on EF Core?

The original linq to entities query is like this:

var result = db.Logs.Select(log => new {
      Date = log.Date,
      Rel = System.Data.Entity.SqlServer.SqlFunctions.DateDiff("day", log.Date, System.Data.Entity.SqlServer.SqlFunctions.GetDate()),
      Dow = System.Data.Entity.SqlServer.SqlFunctions.DatePart("dw", log.Date) - 1 
});
1
6
4/11/2016 4:06:56 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

For the moment, Sql Functions are not supported, here is the opened issue

Provide standard LINQ methods that correspond to standard SQL operations (something like DbFunctions from EF6)

Here are some alternatives you can try:

6
5/23/2017 11:46:36 AM

Popular Answer

It is possible to make use of the datepart SQL function by wrapping it with the DbFunctionAttribute. Same is possible for datediff and so on. Tricky part is to tell ef core not to handle the datepart type parameter as a string. Example:

DbContext:

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }));
}

Query:

repository.GroupBy(x => dbContext.DatePart("week", x.CreatedAt));

some more info: https://github.com/aspnet/EntityFrameworkCore/issues/10404

cyptus

Related Questions

How can I compare a nullable type date using user entered date?

I can't find how to compare a date entered by a user to a date on a table created by EF6. I'm using LINQ Extensions to search the DB and have tried multiple suggestions that compile, but fail during execution....User Input:...DateTime userdate; DateTime.TryParse(search.Value, out userdate); ...Attempts so far:...a = dbContext.Messages.Where(x => x....
c# datetime entity-framework entity-framework-6
asked by Alex Bello

ASP.NET Core & EntityFramework Core: Left (Outer) Join in Linq

If you need to do the ...Left joins... then you have to use ...into... and ...DefaultIfEmpty()... as shown below....var result = from person in _dbContext.Person join detail in _dbContext.PersonDetails on person.Id equals detail.PersonId into Details from m in Details.DefaultIfEmpty() select new ...
asp.net-core entity-framework-core linq
asked by Sampath

implement both EntityFramework 6 and EntityFramework Core side-by-side in a n-tier solution

The EF6 commands don't know how to work with ASP.NET Core projects (they didn't exist when it was written). You'll probably have to unload these projects before running EF6 commands....You may also be able to explicitly specify ...-StartupProject... as the EF6 project to workaround the issue.
asp.net-core entity-framework-6 entity-framework-core
asked by bricelam

C# Entity Framework (.Net Core) validation (server side) how to?

I started to use .net core (asp.net core) with the Entity Framework....I plan to implement a webservice (using asp.net and GraphQL). now i wonder wheter it is possible use the use the RegularExpression Attribute for validation in the Entity Framework....But it seems to be ignored....All samples i found with such attributes was client and server sid...
.net asp.net c# entity-framework entity-framework-core
asked by Marcel Haldemann

Offset/Fetch based paging (Implementation) in EntityFramework (Using LINQ) for SQL Server 2008

This is possible with ...Entity Framework 6.1.2... and above so you should be OK to use it in your project. The standard Skip and Take methods can't be captured in the same way as others. There are now two additional overload of the Skip/Take methods that take lambdas, so instead of this:...var results = context.MyTable .Skip(10) .Take(5); ...
entity-framework entity-framework-6 pagination sql-server sql-server-2008
asked by DavidG

Multiple tenants with Entity Framework Core and SQL Server 2017 in ASP.NET Core application

I have an ASP.NET Core 2.1 application which uses Entity Framework Core 2.1 and SQL Server 2017 (Web edition). I read a lot of how to solve problem with multiple tenants, but more or less the answer is: ...it depends on your application, setup....Setup:...every client gets a new IIS site with own configuration for database...some tables (client de...
asp.net-core entity-framework entity-framework-core sql-server sql-server-2017
asked by Makla

How to do server side pagination on angular with entity framework core

I Skip And Take the data in Controller Class, how to do pagination on angular ...My Controller Class...public IEnumerable<ScrapeTime> scrapeTime(int? page, int pagesize=10) { var countDetails = _context.ScrapeTime.Count(); return _context.ScrapeTime.Skip((page ?? 0) * pagesize).Take(pagesize).ToList(); } ...My component.ts file...constructor(htt...
angular asp.net-core entity-framework-core
asked by user10444877

How to access Entity Framework DbContext entities in Server-Side Blazor Components

I'm new to .NET Core and Blazor, with mostly WebForms and MVC experience....All the Blazor documentation and tutorials I've found use a separate API project and access data through HttpClient and Json serialization/deserialization. I see why this would be necessary for client-side Blazor using WebAssembly, but for ...Server-Side... Blazor using Sig...
blazor blazor-server-side entity-framework-core
asked by Josh Bula

Linq dynamic query (Except - but SQL SERVER side)

Maybe something like this?...Results = context.Entity.Where(x => x.Date > DateTime.Now.AddDays(-3) || x.Type != YourEnum.Complete);
c# entity-framework-core linq sql-server
asked by Matthijs van Bemmelen

SQL query to LINQ expression - Entity Core Framework 3 + SQL Server

DECLARE @thirtyDaysAgo DATETIME = getdate()-30 SELECT sum(case when CreatedWhen >= @thirtyDaysAgo then 1 else 0 end) lessThan30, sum(case when CreatedWhen < @thirtyDaysAgo then 1 else 0 end) Greaterthan30 FROM ThisAwesomeTable ...Cannot figure out how to convert this to a fluent query....The intent is to count the number of rows that were created...
entity-framework-core fluent linq sql-server
asked by Ashwin

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow