public Task <T> FirstOrDefaultByCompileQueryAsync <T>(Expression <Func <T, bool> > filter) where T : class
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileAsyncQuery((DbContext context) => context.Set <T>().AsNoTracking().FirstOrDefault(filter))(this));
}
/// <summary>
/// 统计数量Count()，该方法是显式编译的查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public Task <int> CountByCompileQueryAsync <T>(Expression <Func <T, bool> > filter) where T : class
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileAsyncQuery((DbContext context) => context.Set <T>().Count(filter))(this));
}
public Task <List <T> > GetByCompileQueryAsync <T>(Expression <Func <T, bool> > filter) where T : class
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileAsyncQuery((DbContext context) => context.Set <T>().AsNoTracking().Where(filter).ToList())(this));
}
private static void Main()
{
// Warmup
using (var db = new AdventureWorksContext())
{
var customer = db.Customers.First();
}
RunTest(
accountNumbers =>
{
using (var db = new AdventureWorksContext())
{
foreach (var id in accountNumbers)
{
// Use a regular auto-compiled query
var customer = db.Customers.First(c => c.AccountNumber == id);
}
}
},
name: "Regular");
RunTest(
accountNumbers =>
{
// Create explicit compiled query
var query = EF.CompileQuery((AdventureWorksContext context, string id)
=> context.Customers.First(c => c.AccountNumber == id));
using (var db = new AdventureWorksContext())
{
foreach (var id in accountNumbers)
{
// Invoke the compiled query
var customer = query(db, id);
}
}
},
name: "Compiled");
RunTest(
accountNumbers =>
{
// Create explicit compiled query
var query = EF.CompileAsyncQuery((AdventureWorksContext context, string id)
=> context.Customers.First(c => c.AccountNumber == id));
using (var db = new AdventureWorksContext())
{
foreach (var id in accountNumbers)
{
// Invoke the compiled query
var customer = query(db, id);
}
}
},
name: "Async Compiled");
}
/// <summary>
/// The main.
/// </summary>
/// <param name="args">
/// The args.
/// </param>
private static void Main(string[] args)
{
/*******************************************************
* Make sure to apply migration before running this app
*******************************************************/
userIds = GetUserIds(1000);
RunTest(
ids =>
{
using (var db = new UserDbContext())
{
foreach (var id in userIds)
{
var user = db.Users.Single(c => c.Id == id);
}
}
},
"Regular Query");
RunTest(
async ids =>
{
var query = EF.CompileAsyncQuery((UserDbContext db, int id)
=> db.Users.Single(c => c.Id == id));
using (var db = new UserDbContext())
{
foreach (var id in userIds)
{
// var user = db.Users.Single(c => c.Id == id);
var user = await query(db, id); // <-- Amazing performance
}
}
},
"Async Comp Qry");
RunTest(
ids =>
{
var query = EF.CompileQuery((UserDbContext db, int id)
=> db.Users.Single(c => c.Id == id));
using (var db = new UserDbContext())
{
foreach (var id in userIds)
{
// var user = db.Users.Single(c => c.Id == id);
var user = query(db, id);
}
}
},
"Compiled Query");
Console.WriteLine("\n\nPress any key to exit.");
Console.Read();
}
public static async Task ReadAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
try
{
await InsertAsync(context, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
//var existingEntities = await context.Set<T>().FromSql(sqlQuery).ToListAsync(cancellationToken);
Expression <Func <DbContext, IQueryable <T> > > expression = null;
if (tableInfo.BulkConfig.TrackingEntities)
{
expression = (ctx) => ctx.Set <T>().FromSqlRaw(sqlQuery);
}
else
{
expression = (ctx) => ctx.Set <T>().FromSqlRaw(sqlQuery).AsNoTracking();
}
var compiled = EF.CompileAsyncQuery(expression);
var existingEntities = (await compiled(context).ToListAsync(cancellationToken).ConfigureAwait(false));
tableInfo.UpdateReadEntities(entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database
.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName),
cancellationToken).ConfigureAwait(false);
}
}
}
// -- Sqlite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
throw new NotImplementedException();
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
public virtual async Task Keyless_query_first_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context)
=> context.CustomerQueries.OrderBy(c => c.CompanyName).First());
using (var context = CreateContext())
{
Assert.Equal("Alfreds Futterkiste", (await query(context)).CompanyName);
}
}
public virtual async Task DbSet_query_first_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context)
=> context.Customers.OrderBy(c => c.CustomerID).First());
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context)).CustomerID);
}
}
public virtual async Task Query_with_closure_async_null()
{
string customerID = null;
var query = EF.CompileAsyncQuery(
(NorthwindContext context)
=> context.Customers.Where(c => c.CustomerID == customerID));
using (var context = CreateContext())
{
Assert.Empty(await query(context).ToListAsync());
}
}
private Func <DbContext, CancellationToken, string, Task <TReadModel> > CompileQueryById(bool tracking)
{
return(tracking
? EF.CompileAsyncQuery((DbContext dbContext, CancellationToken t, string id) =>
dbContext
.Set <TReadModel>()
.AsTracking()
.SingleOrDefault(e => EF.Property <string>(e, Key) == id))
: EF.CompileAsyncQuery((DbContext dbContext, CancellationToken t, string id) =>
dbContext
.Set <TReadModel>()
.AsNoTracking()
.SingleOrDefault(e => EF.Property <string>(e, Key) == id)));
}
public virtual async Task Untyped_context_async()
{
var query = EF.CompileAsyncQuery((DbContext context) => context.Set <Customer>());
using (var context = CreateContext())
{
Assert.Equal(91, (await query(context).ToListAsync()).Count);
}
using (var context = CreateContext())
{
Assert.Equal(91, (await query(context).ToListAsync()).Count);
}
}
public virtual async Task DbSet_query_async()
{
var query = EF.CompileAsyncQuery((NorthwindContext context) => context.Customers);
using (var context = CreateContext())
{
Assert.Equal(91, (await query(context).ToListAsync()).Count);
}
using (var context = CreateContext())
{
Assert.Equal(91, (await query(context).ToListAsync()).Count);
}
}
public virtual async Task Query_with_array_parameter_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context, string[] args)
=> context.Customers.Where(c => c.CustomerID == args[0]));
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context, new[] { "ALFKI" }).ToListAsync()).First().CustomerID);
}
using (var context = CreateContext())
{
Assert.Equal("ANATR", (await query(context, new[] { "ANATR" }).ToListAsync()).First().CustomerID);
}
}
public virtual async Task Query_with_three_parameters_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context, object _, int __, string customerID)
=> context.Customers.Where(c => c.CustomerID == customerID));
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context, null, 1, "ALFKI").ToListAsync()).First().CustomerID);
}
using (var context = CreateContext())
{
Assert.Equal("ANATR", (await query(context, null, 1, "ANATR").ToListAsync()).First().CustomerID);
}
}
public virtual async Task First_query_with_single_parameter_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context, string customerID)
=> context.Customers.First(c => c.CustomerID == customerID));
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context, "ALFKI")).CustomerID);
}
using (var context = CreateContext())
{
Assert.Equal("ANATR", (await query(context, "ANATR")).CustomerID);
}
}
public static async Task CompileQueryAsync()
{
Console.WriteLine(nameof(CompileQueryAsync));
Func <BooksContext, string, AsyncEnumerable <Book> > query = EF.CompileAsyncQuery <BooksContext, string, Book>((context, publisher) => context.Books.Where(b => b.Publisher == publisher));
using (var context = new BooksContext())
{
AsyncEnumerable <Book> books = query(context, "Wrox Press");
await books.ForEachAsync(b =>
{
Console.WriteLine($"{b.Title} {b.Publisher}");
});
}
Console.WriteLine();
}
public virtual async Task Query_with_array_parameter_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context, string[] args)
=> context.Customers.Where(c => c.CustomerID == args[0]));
using (var context = CreateContext())
{
await Enumerate(query(context, new[] { "ALFKI" }));
}
using (var context = CreateContext())
{
await Enumerate(query(context, new[] { "ANATR" }));
}
}
public async Task <bool> GetSingleColumnSlowAsync(Stopwatch watch, int repeatCount, int takeCount)
{
var query = EF.CompileAsyncQuery((EFCoreContext db) =>
db.Narrow.Where(t => t.ID == 1).Select(t => t.ID).First());
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new EFCoreContext(TrackChanges))
await query(db);
}
watch.Stop();
return(true);
}
public async Task <bool> GetWideListAsync(Stopwatch watch, int repeatCount, int takeCount)
{
var query = EF.CompileAsyncQuery((EFCoreContext db, int top) =>
db.WideLong.Take(top));
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new EFCoreContext(TrackChanges))
await query(db, takeCount).ForEachAsync(item => {});
}
watch.Stop();
return(true);
}
public async Task <bool> GetSingleColumnParamAsync(Stopwatch watch, int repeatCount, int takeCount)
{
var query = EF.CompileAsyncQuery((EFCoreContext db, int id, int p) =>
db.Narrow.Where(t => t.ID == id && t.Field1 == p).Select(t => t.ID).First());
watch.Start();
using (var db = new EFCoreContext(TrackChanges))
for (var i = 0; i < repeatCount; i++)
{
await query(db, 1, 2);
}
watch.Stop();
return(true);
}
public virtual async Task First_query_with_cancellation_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context, string customerID, CancellationToken ct)
=> context.Customers.First(c => c.CustomerID == customerID));
var cancellationToken = default(CancellationToken);
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context, "ALFKI", cancellationToken)).CustomerID);
}
using (var context = CreateContext())
{
Assert.Equal("ANATR", (await query(context, "ANATR", cancellationToken)).CustomerID);
}
}
} // GetProductByIdAsync
public async Task <Category> GetCategoryByIdAsync(int id)
{
Category category = await EF.CompileAsyncQuery((ApplicationDbContext db, int paramId)
=> context.Categories.AsNoTracking()
.Include(c => c.ProductSpecificationNumbersInOrder)
.ThenInclude(n => n.ProductSpecification)
.FirstOrDefault(c => c.Id == paramId)
).Invoke(context, id);
if (category != null)
{
category.ProductSpecificationNumbersInOrder = category.ProductSpecificationNumbersInOrder
.OrderBy(n => n.NumberInOrder)
.ToList();
} // if
return(category);
} // GetCategoryByIdAsync
public virtual async Task Query_with_closure_async()
{
var customerID = "ALFKI";
var query = EF.CompileAsyncQuery((NorthwindContext context)
=> context.Customers.Where(c => c.CustomerID == customerID));
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context).ToListAsync()).First().CustomerID);
}
customerID = "ANATR";
using (var context = CreateContext())
{
Assert.Equal("ALFKI", (await query(context).ToListAsync()).First().CustomerID);
}
}
public IActionResult Index()
{
Stopwatch Sw = new Stopwatch();
Sw.Start();
var Query = EF.CompileAsyncQuery((BookShopContext Context, int id)
=> Context.Books.SingleOrDefault(b => b.BookID == id));
for (int i = 0; i < 1000; i++)
{
//var Book = _context.Books.SingleOrDefault(b => b.BookID == i);
var Book = Query(_context, i);
}
Sw.Stop();
return(View(Sw.ElapsedMilliseconds));
}
//已编译查询
public static async Task CompileQueryAsync()
{
Console.WriteLine(nameof(CompileQueryAsync));
//定义已编译查询
Func <BooksContext, string, IAsyncEnumerable <Book> > query =
EF.CompileAsyncQuery <BooksContext, string, Book>((context, publisher) => context.Books.Where(b => b.Publisher == publisher));
using (var context = new BooksContext())
{
//使用IAsyncEnumerable接口类型，直接使用await foreach(var item in collection)
IAsyncEnumerable <Book> books = query(context, "Wrox Press");
await foreach (var b in books)
{
Console.WriteLine($"{b.Title} {b.Publisher}");
}
;
}
Console.WriteLine();
}
public async Task <IEnumerable <CategoryDto> > Handle(GetCategoriesQuery request,
CancellationToken cancellationToken)
{
if (request == null)
{
throw new ArgumentNullException(nameof(request));
}
return(await _redisCacheService.GetOrSetAsync(CacheKeys.CategoriesKey, async() =>
{
Func <MainDbContext, IAsyncEnumerable <Category> > getCategoriesHandler =
EF.CompileAsyncQuery((MainDbContext context) => context.Categories.AsNoTracking());
var categories = await getCategoriesHandler(_dbContext).ToListAsync(cancellationToken);
return categories.Select(x => new CategoryDto {
Id = x.Id, Name = x.Name
});
}));
}
public virtual async Task FromSqlRaw_queryable_composed_compiled_with_parameter(bool async)
{
if (async)
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context) => CosmosQueryableExtensions.FromSqlRaw(
context.Set <Customer>(),
@"SELECT * FROM root c WHERE c[""Discriminator""] = ""Customer"" AND c[""CustomerID""] = {0}", "CONSH")
.Where(c => c.ContactName.Contains("z")));
using (var context = CreateContext())
{
var actual = await query(context).ToListAsync();
Assert.Single(actual);
}
}
else
{
var query = EF.CompileQuery(
(NorthwindContext context) => CosmosQueryableExtensions.FromSqlRaw(
context.Set <Customer>(),
@"SELECT * FROM root c WHERE c[""Discriminator""] = ""Customer"" AND c[""CustomerID""] = {0}", "CONSH")
.Where(c => c.ContactName.Contains("z")));
using (var context = CreateContext())
{
var actual = query(context).ToArray();
Assert.Single(actual);
}
}
AssertSql(
@"SELECT c
FROM (
SELECT * FROM root c WHERE c[""Discriminator""] = ""Customer"" AND c[""CustomerID""] = ""CONSH""
) c
WHERE CONTAINS(c[""ContactName""], ""z"")");
}
public override async Task Query_with_array_parameter_async()
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context, string[] args)
=> context.Customers.Where(c => c.CustomerID == args[0]));
using (var context = CreateContext())
{
Assert.Equal(
CoreStrings.TranslationFailed("DbSet<Customer>() .Where(c => c.CustomerID == __args[0])"),
(await Assert.ThrowsAsync <InvalidOperationException>(
() => Enumerate(query(context, new[] { "ALFKI" })))).Message.Replace("\r", "").Replace("\n", ""));
}
using (var context = CreateContext())
{
Assert.Equal(
CoreStrings.TranslationFailed("DbSet<Customer>() .Where(c => c.CustomerID == __args[0])"),
(await Assert.ThrowsAsync <InvalidOperationException>(
() => Enumerate(query(context, new[] { "ANATR" })))).Message.Replace("\r", "").Replace("\n", ""));
}
}
public async Task <Product> GetProductByIdAsync(int id)
{
Product product = await EF.CompileAsyncQuery((ApplicationDbContext db, int paramId)
=> context.Products.AsNoTracking()
.Include(p => p.Category)
.Include(p => p.Manufacturer)
.Include(p => p.ProductSpecificationValues)
.ThenInclude(psv => psv.ProductSpecification)
.ThenInclude(ps => ps.ProductSpecificationNumbersInOrder)
.FirstOrDefault(p => p.Id == paramId)
).Invoke(context, id);
if (product != null)
{
product.ProductSpecificationValues = product.ProductSpecificationValues
.OrderBy(psv => psv.ProductSpecification.ProductSpecificationNumbersInOrder
.First(n => n.CategoryId == product.CategoryId).NumberInOrder)
.ToList();
} // if
return(product);
} // GetProductByIdAsync
public async Task FromSqlRaw_queryable_composed_compiled(bool async)
{
if (async)
{
var query = EF.CompileAsyncQuery(
(NorthwindContext context) => context.Set <Customer>()
.FromSqlRaw(@"SELECT * FROM root c WHERE c[""Discriminator""] = ""Customer""")
.Where(c => c.ContactName.Contains("z")));
using (var context = CreateContext())
{
var actual = await query(context).ToListAsync();
Assert.Equal(14, actual.Count);
}
}
else
{
var query = EF.CompileQuery(
(NorthwindContext context) => context.Set <Customer>()
.FromSqlRaw(@"SELECT * FROM root c WHERE c[""Discriminator""] = ""Customer""")
.Where(c => c.ContactName.Contains("z")));
using (var context = CreateContext())
{
var actual = query(context).ToArray();
Assert.Equal(14, actual.Length);
}
}
AssertSql(
@"SELECT c
FROM (
SELECT * FROM root c WHERE c[""Discriminator""] = ""Customer""
) c
WHERE CONTAINS(c[""ContactName""], ""z"")");
}
