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")); } }