Modern databases have built-in support for JSON although the specifics vary from database to database, all support some basic forms of parsing and querying JSON directly in SQL. This SQL is a completely different beast indeed but even without understanding exactly what’s going on, we can already see that the blog names are passed as a parameter, represented via in the SQL – similar to our PostgreSQL translation above. Let’s see what SQL preview4 generates for this LINQ query: Executed DbCommand (49ms) (Size = 4000)], CommandType='Text', CommandTimeout='30'] Using OpenJson to translate parameter collections We clearly need a better solution for translating the LINQ Contains operator when the collection is a parameter. In short – not great! In fact, this performance issue is the second most highly-voted issue in the EF Core repo and as with most performance problems, your application may be suffering from it without you knowing about it. That means you’re also evicting cache entries for other, important SQLs that will need to be used, and requiring them to be re-planned again and again. If you frequently use Contains with a variable array, each individual invocation causees valuable cache entries to be taken at the database, for SQLs that will most probably never be used (since they have the specific array values baked in). SQL Server (and Npgsql) can only cache a certain number of SQLs at some point, they have to get rid of old entries to avoid using too much memory. In addition, EF itself has an internal SQL cache for its queries, and this SQL variance makes caching impossible, leading to further EF overhead for each and every query.īut crucially, the negative performance impact of constantly varying SQLs goes beyond this particular query. This is vital for good performance: SQL Server caches SQL, performing expensive query planning only the first time a particular SQL is seen (a similar SQL cache is implemented in the database driver for PostgreSQL). This has the unfortunate consequence that the SQL produced by EF varies for different array contents – a pretty abnormal situation! Usually, when you run the same LINQ query over and over again – changing only parameter values – EF sends the exact same SQL to the database. NET variables in EF LINQ queries usually become SQL parameters, in this particular case the variable has disappeared, and its contents have been inserted directly into the SQL. īut wait, this looks suspiciously familiar – it’s the inline collection translation we saw above! And indeed, since we couldn’t parameterize the array, we simply embedded its values – as constants – into the SQL query. Up to now, all versions of EF have provided the following translation: SELECT. But what can we do for other databases, where this does not exist? Leveraging this, we pass the array of blog names as a SQL parameter directly to ANY – that’s – and get the perfect translation. WHERE b."Name" = ANY is very similar to the inline collection translation above with IN, but uses the PostgreSQL-specific ANY construct, which can accept an array type. Where(b => new (DbType = Object)], CommandType='Text', CommandTimeout='30'] You could use the following LINQ query to do so: var blogs = await context.Blogs Starting from something simple, imagine you have a bunch of Blogs, and want to query out two Blogs whose names you know. Let’s examine such a case, which also happens to be related to a highly-voted EF performance issue. In EF’s quest to translate more and more LINQ queries to SQL, we sometimes encounter odd and problematic corner cases. Let’s dive in! Translating LINQ Contains with an inline collection The fourth preview version of EF Core 8.0 preview4 includes some exciting new capabilities in query translation, as well as an important performance optimization. NET 8 as a long-term support (LTS) release. NET 8 as we near release.ĮF8 will align with. NET 6, and can therefore be used with either. Import fourth preview of Entity Framework Core (EF Core) 8 is available on NuGet today! Basic informationĮF Core 8, or just EF8, is the successor to EF Core 7, and is scheduled for release in November 2023, at the same time as. Keep the database in assets folder and then follow the below: It is assumed that you have a copy of the database in the assets folder, so for example, if your database name is ordersDB, then the value of DB_NAME will be ordersDB, private static String DB_NAME ="ordersDB" Before trying this code, please find this line in the below code: private static String DB_NAME ="YourDbName" // Database nameĭB_NAME here is the name of your database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |