امکان تعریف توابع خاص بانکهای اطلاعاتی در EF Core
نویسنده: وحید نصیری
تاریخ: ۱۳۹۹/۰۲/۳۱ ۹:۰
آدرس: www.dntips.ir
| مطالب | ۳۶۹۴ |
| نویسندگان | ۲۷۶ |
| گروههای مطالب | ۱۰۲۴ |
| نقشههای راه | ۱۱۹ |
| دورهها | ۱۴ |
| اشتراکها | ۱۷۹۱۴ |
using System;
namespace EFCoreDbFunctionsSample.Entities
{
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime AddDate { get; set; }
}
} var usersInfo = context.People.Where(person => (DateTime.Now - person.AddDate).Days <= 10).ToList();
'The LINQ expression 'DbSet<Person>.Where(p => (DateTime.Now - p.AddDate).Days <= 10)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
SELECT [p].[Id], [p].[AddDate], [p].[Name] FROM [People] AS [p] WHERE DATEDIFF(Day, [p].[AddDate], GETDATE()) <= 10
namespace EFCoreDbFunctionsSample.DataLayer
{
public enum SqlDateDiff
{
Year,
Quarter,
Month,
DayOfYear,
Day,
Week,
Hour,
Minute,
Second,
MilliSecond,
MicroSecond,
NanoSecond
}
public static class SqlDbFunctionsExtensions
{
public static int SqlDateDiff(SqlDateDiff interval, DateTime initial, DateTime end)
=> throw new InvalidOperationException($"{nameof(SqlDateDiff)} method cannot be called from the client side.");
public static readonly MethodInfo SqlDateDiffMethodInfo = typeof(SqlDbFunctionsExtensions)
.GetRuntimeMethod(
nameof(SqlDbFunctionsExtensions.SqlDateDiff),
new[] { typeof(SqlDateDiff), typeof(DateTime), typeof(DateTime) }
);
}
} namespace EFCoreDbFunctionsSample.DataLayer
{
public class ApplicationDbContext : DbContext
{
// ...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.HasDbFunction(SqlDbFunctionsExtensions.SqlDateDiffMethodInfo)
.HasTranslation(args =>
{
var parameters = args.ToArray();
var param0 = ((SqlConstantExpression)parameters[0]).Value.ToString();
return SqlFunctionExpression.Create("DATEDIFF",
new[]
{
new SqlFragmentExpression(param0), // It should be written as DateDiff(day, ...) and not DateDiff(N'day', ...) .
parameters[1],
parameters[2]
},
SqlDbFunctionsExtensions.SqlDateDiffMethodInfo.ReturnType,
typeMapping: null);
});
}
}
} var sinceDays = 10;
users = context.People.Where(person =>
SqlDbFunctionsExtensions.SqlDateDiff(SqlDateDiff.Day, person.AddDate, DateTime.Now) <= sinceDays).ToList();
/*
SELECT [p].[Id], [p].[AddDate], [p].[Name]
FROM [People] AS [p]
WHERE DATEDIFF(Day, [p].[AddDate], GETDATE()) <= @__sinceDays_0
*/ var users = context.People.Where(x => x.AddDate >= DateTime.Now.AddDays(-10)).ToList();