protected IEnumerable <T> QueryOutputTable <T>(DbContext context, string sqlQuery) where T : class
{
var compiled = EF.CompileQuery(GetQueryExpression <T>(sqlQuery));
var result = compiled(context);
return(result);
}
static void Main(string[] args)
{
// Warmup
using (var db = new AdventureWorksContext())
{
var customer = db.Customers.First();
}
RunTest(
regularTest: (accountNumbers) =>
{
using (var db = new AdventureWorksContext())
{
foreach (var id in accountNumbers)
{
var customer = db.Customers.Single(c => c.AccountNumber == id);
}
}
},
compiledTest: (accountNumbers) =>
{
var query = EF.CompileQuery((AdventureWorksContext db, string id) =>
db.Customers.Single(c => c.AccountNumber == id));
using (var db = new AdventureWorksContext())
{
foreach (var id in accountNumbers)
{
var customer = query(db, id);
}
}
});
}
public static string GetBooksByCategory(BookShopContext context, string input)
{
var query = EF.CompileQuery <BookShopContext, string, IEnumerable <string> >(
(db, categoryText) => db.Books
.Where(b => b.BookCategories.Any(bk => bk.Category.Name.ToLower() == categoryText.ToLower()))
.Select(b => b.Title)
);
var categories = input.Split(" ");
var sb = new StringBuilder();
var result = new List <string>();
foreach (var category in categories)
{
var current = query(context, category);
result.AddRange(current);
}
foreach (var line in result.OrderBy(t => t))
{
sb.AppendLine(line);
}
return(sb.ToString().TrimEnd());
}
public bool ComplicatedLinqFast(Stopwatch watch, int repeatCount, int takeCount)
{
var query = EF.CompileQuery((EFCoreContext db, int top) =>
(
from n1 in db.Narrow
join n2 in db.Narrow on new { n1.ID, n1.Field1 } equals new { n2.ID, n2.Field1 }
where n1.ID < 100 && n2.Field1 <= 50
group n1 by n1.ID into gr
select new
{
gr.Key,
Count = gr.Count()
}
)
.OrderBy(n1 => n1.Key)
.Skip(1)
.Take(top));
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new EFCoreContext(TrackChanges))
foreach (var item in query(db, takeCount))
{
}
}
watch.Stop();
return(true);
}
static void Main(string[] args)
{
var builder = new DbContextOptionsBuilder <BloggingContext>();
//var connectionString = configuration.GetConnectionString("DefaultConnection");
var connection = @"Server=(local);Database=TestEFCoreCache;Trusted_Connection=True;ConnectRetryCount=0";
builder.UseSqlServer(connection);
//builder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
MemoryCache _cache = new MemoryCache(new MemoryCacheOptions()
{
});
builder.UseMemoryCache(_cache);
BloggingContext db = new BloggingContext(builder.Options);
var qryposts = EF.CompileQuery((BloggingContext dbC, int id)
=> db.Posts.FirstOrDefault(p => p.PostId == id));
for (int i = 0; i < 100000; i++)
{
//var posts1 = db.Posts.Where(p => p.PostId == 2);
//posts1.ToList();
//var posts = qryposts(db, 2);
//posts.ToList();
var posts = GetPosts(db, _cache);
Console.WriteLine(i.ToString());
}
Console.ReadKey();
}
public override void Query_with_array_parameter()
{
var query = EF.CompileQuery(
(NorthwindContext context, string[] args)
=> context.Customers.Where(c => c.CustomerID == args[0]));
using (var context = CreateContext())
{
// We still throw an InvalidOperationException, which is expected, but with a different error message,
// because of the implemented array support for JSON.
Assert.Throws <InvalidOperationException>(
() => query(context, new[] { "ALFKI" })
.First()
.CustomerID);
}
using (var context = CreateContext())
{
// We still throw an InvalidOperationException, which is expected, but with a different error message,
// because of the implemented array support for JSON.
Assert.Throws <InvalidOperationException>(
() => query(context, new[] { "ANATR" })
.First()
.CustomerID);
}
}
protected IEnumerable <T> QueryOutputTable(string sqlQuery)
{
var compiled = EF.CompileQuery(GetQueryExpression(sqlQuery));
var result = compiled(Context);
return(result);
}
private IEnumerable <T> CompileQueryPage(IQueryable <T> orderQueryable, Expression <Func <T, bool> > exp, int pageIndex, int pageSize)
{
var func = EF.CompileQuery((AppDbContext context, Expression <Func <T, bool> > exps) => orderQueryable.Where(exp).
Skip((pageIndex - 1) * pageSize).Take(pageSize));
return(func(db, exp));
}
protected IEnumerable QueryOutputTable(DbContext context, Type type, string sqlQuery)
{
var compiled = EF.CompileQuery(GetQueryExpression(type, sqlQuery));
var result = compiled(context);
return(result);
}
public static void Read <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
try
{
Insert(context, entities, tableInfo, progress);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
//var existingEntities = context.Set<T>().FromSql(q).AsNoTracking().ToList(); // Not used because of EF Memory leak bug
Expression <Func <DbContext, IQueryable <T> > > expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery).AsNoTracking();
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
var existingEntities = compiled(context).ToList();
tableInfo.UpdateReadEntities(entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
}
/// <summary>
/// 查询单个
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
private T CompileQuerySingle(Expression <Func <T, bool> > exp)
{
var func = EF.CompileQuery((RepositoryContext context, Expression <Func <T, bool> > exps) =>
context.Set <T>().FirstOrDefault(exp));
return(func(_context, exp));
}
public UserDto GetCompiledQuerry(Guid id)
{
Func <TestDbContext, User> QueryUserById = EF.CompileQuery <TestDbContext, User>(context => context.Users.FirstOrDefault(usr => usr.Id.Equals(id)));
User user = QueryUserById(this.context);
return(this.mapper.Map <UserDto>(user));
}
public IEnumerable <TEntity> GetByCompileQuery(Expression <Func <TEntity, bool> > filter)
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileQuery((BaseDbContext context) => context.Set <TEntity>().AsNoTracking().Where(filter).AsEnumerable())(_dbContext));
}
public int CountByCompileQuery <T>(Expression <Func <T, bool> > filter) where T : class
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileQuery((DbContext context) => context.Set <T>().Count(filter))(this));
}
public T FirstOrDefaultWithTrackingByCompileQuery <T>(Expression <Func <T, bool> > filter) where T : class
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileQuery((DbContext context) => context.Set <T>().FirstOrDefault(filter))(this));
}
public IList <T> GetByCompileQuery <T>(Expression <Func <T, bool> > filter) where T : class
{
if (filter == null)
{
filter = m => true;
}
return(EF.CompileQuery((DbContext context) => context.Set <T>().AsNoTracking().Where(filter).ToList())(this));
}
public Category GetCategoryCompile()
{
return(EF.CompileQuery((TestDbContext ctx) =>
ctx.Categories.Include(c => c.Parent)
.Where(c => c.Parent == null)
.OrderBy(c => c.Name)
.FirstOrDefault()).Invoke(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();
}
private static void Main()
{
using (var db = new AdventureWorksContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
foreach (var accountNumber in GetAccountNumbers(2000))
{
db.Add(new Customer {
AccountNumber = accountNumber,
});
}
db.SaveChanges();
}
// 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.Single(c => c.AccountNumber == id);
}
}
},
name: "Regular");
RunTest(
accountNumbers =>
{
// Create an explicit compiled query
var query = EF.CompileQuery(
(AdventureWorksContext db, string id)
=> db.Customers.Single(c => c.AccountNumber == id));
using (var db = new AdventureWorksContext())
{
foreach (var id in accountNumbers)
{
// Invoke the compiled query
query(db, id);
}
}
},
name: "Compiled");
}
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.ExecuteSqlRawAsync(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); // TempFIX
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
var existingEntities = compiled(context).ToList();
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 void Keyless_query_first()
{
var query = EF.CompileQuery(
(NorthwindContext context) => context.CustomerQueries.OrderBy(c => c.CompanyName).First());
using (var context = CreateContext())
{
Assert.Equal("Alfreds Futterkiste", query(context).CompanyName);
}
}
public IEnumerable <T> FindList <key>(Expression <Func <T, bool> > query, Expression <Func <T, key> > order, out int total, bool sort, int pageIndex, int pageSize)
{
//var func=EF.CompileQuery((DbContext context,Expression<Func<T,bool>> qy, Expression<Func<T, key>> od) => context.Set<T>().Where(qy).OrderByDescending(order).Skip((pageIndex - 1)).Take(pageSize));
//total = GetTotal(query);
//return func(_db, query, order);
var func = EF.CompileQuery((DbContext context) => context.Set <T>().Where(query).OrderBy(order).Skip((pageIndex)).Take(pageSize));
total = GetTotal(query);
return(func(_db));
}
public virtual void DbSet_query_first()
{
var query = EF.CompileQuery(
(NorthwindContext context) => context.Customers.OrderBy(c => c.CustomerID).First());
using (var context = CreateContext())
{
Assert.Equal("ALFKI", query(context).CustomerID);
}
}
static IEnumerable <Blog> FindBlogCompiled(string term)
{
var likeExpr = $"%{term}%";
var query = EF.CompileQuery((BlogDb db, string expr) => db.Blogs.Where(b => EF.Functions.Like(b.Url, expr)));
using (var db = new BlogDb("john"))
{
return(query(db, likeExpr).ToArray());
}
}
private static async Task ReadAsync <T>(DbContext context, Type type, 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.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
try
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, 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 static void Read <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
try
{
Insert(context, entities, tableInfo, progress);
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
//var existingEntities = context.Set<T>().FromSql(q).AsNoTracking().ToList(); // Not used because of EF Memory leak bug
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.CompileQuery(expression); // instead using Compiled queries
var existingEntities = compiled(context).ToList();
tableInfo.UpdateReadEntities(entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
}
// -- Sqlite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
throw new NotImplementedException();
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
public void CanCompileQueries()
{
var query = EF.CompileQuery <GameDroneContext, IEnumerable <Player> >(ctx => ctx.Players.OrderBy(x => x.Name));
using (var ctx = Build())
{
var players = query(ctx).ToList();
Assert.NotEmpty(players);
}
}
public virtual void Compiled_query()
{
var query = EF.CompileQuery(
(NorthwindContext context, string customerID)
=> context.Customers.Where(c => c.CustomerID == customerID));
Assert.Equal("BERGS", query(_context, "BERGS").First().CustomerID);
using var context = CreateContext();
Assert.Equal("BLAUS", query(context, "BLAUS").First().CustomerID);
}
public virtual void Compiled_query_when_does_not_end_in_query_operator()
{
var query = EF.CompileQuery(
(NorthwindContext context, string customerID)
=> context.Customers.Where(c => c.CustomerID == customerID).Count() == 1);
using (var context = CreateContext())
{
Assert.True(query(context, "ALFKI"));
}
}
