{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12552","repository_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore","labels_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12552/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12552/comments","events_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12552/events","html_url":"https://github.com/aspnet/EntityFrameworkCore/issues/12552","id":338351652,"node_id":"MDU6SXNzdWUzMzgzNTE2NTI=","number":12552,"title":"SQL translation should be more generic and operate on end results","user":{"login":"mqudsi","id":606923,"node_id":"MDQ6VXNlcjYwNjkyMw==","avatar_url":"https://avatars3.githubusercontent.com/u/606923?v=4","gravatar_id":"","url":"https://api.github.com/users/mqudsi","html_url":"https://github.com/mqudsi","followers_url":"https://api.github.com/users/mqudsi/followers","following_url":"https://api.github.com/users/mqudsi/following{/other_user}","gists_url":"https://api.github.com/users/mqudsi/gists{/gist_id}","starred_url":"https://api.github.com/users/mqudsi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mqudsi/subscriptions","organizations_url":"https://api.github.com/users/mqudsi/orgs","repos_url":"https://api.github.com/users/mqudsi/repos","events_url":"https://api.github.com/users/mqudsi/events{/privacy}","received_events_url":"https://api.github.com/users/mqudsi/received_events","type":"User","site_admin":false},"labels":[{"id":936619505,"node_id":"MDU6TGFiZWw5MzY2MTk1MDU=","url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/labels/customer-reported","name":"customer-reported","color":"a4a5f9","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":27,"created_at":"2018-07-04T18:18:11Z","updated_at":"2018-07-13T22:31:13Z","closed_at":null,"author_association":"NONE","body":"Following up on the discussion in #12517, I believe there's a fundamental issue with the way SQL translation is currently handled. If there is an expression that cannot be directly mapped to an SQL equivalent, but that expression evaluates to a data type that _can_ be directly plugged in to an SQL query, then that expression should be evaluated and its result used instead of the SQL translation giving up and the query being evaluated locally rather than on the database server.\r\n\r\nTo illustrate with a concrete example:\r\n\r\n```cs\r\n    public class Database : DbContext\r\n    {\r\n        ....\r\n        public IQueryable<Listing> ActiveListings => Listings\r\n            .Where(x => x.Status != ListingStatus.Sold)\r\n            .OrderByDescending(x => x.RenewTime)\r\n            .Where(x => x.RenewTime > DateTime.UtcNow.AddDays(-7));\r\n    }\r\n```\r\n\r\nIn the latest published EFCore w/ SQLite provider releases, this cannot be evaluated in the database as `AddDays(xx)` does not have a translation implemented.\r\n\r\nHowever, simply doing the following makes SQL translation work and the query is fully evaluated on the DB server rather than the ASP.NET Core host:\r\n\r\n```cs\r\n    public class Database : DbContext\r\n    {\r\n        ....\r\n        DateTime ExpiryCompare => DateTime.UtcNow.AddDays(-7);\r\n        public IQueryable<Listing> ActiveListings => Listings\r\n            .Where(x => x.Status != ListingStatus.Sold)\r\n            .OrderByDescending(x => x.RenewTime)\r\n            .Where(x => x.RenewTime > ExpiryCompare);\r\n    }\r\n```\r\n\r\nMore generally, if the translation of a given subexpression does not have a translation expressly provided by the provider, and that subexpression does not depend on the value of the record being evaluated (i.e. for a lambda `x => x == Foo(..)`, `Foo` does not utilize the value of `x`) and the return type of `Foo(...)` _can_ be used to directly evaluate the expression on the database server, then that subexpression should be evaluated and its result plugged in before translation is attempted once more."},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/comments/404969941","html_url":"https://github.com/aspnet/EntityFrameworkCore/issues/12552#issuecomment-404969941","issue_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12552","id":404969941,"node_id":"MDEyOklzc3VlQ29tbWVudDQwNDk2OTk0MQ==","user":{"login":"jzabroski","id":447485,"node_id":"MDQ6VXNlcjQ0NzQ4NQ==","avatar_url":"https://avatars2.githubusercontent.com/u/447485?v=4","gravatar_id":"","url":"https://api.github.com/users/jzabroski","html_url":"https://github.com/jzabroski","followers_url":"https://api.github.com/users/jzabroski/followers","following_url":"https://api.github.com/users/jzabroski/following{/other_user}","gists_url":"https://api.github.com/users/jzabroski/gists{/gist_id}","starred_url":"https://api.github.com/users/jzabroski/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jzabroski/subscriptions","organizations_url":"https://api.github.com/users/jzabroski/orgs","repos_url":"https://api.github.com/users/jzabroski/repos","events_url":"https://api.github.com/users/jzabroski/events{/privacy}","received_events_url":"https://api.github.com/users/jzabroski/received_events","type":"User","site_admin":false},"created_at":"2018-07-13T22:31:13Z","updated_at":"2018-07-13T22:31:13Z","author_association":"NONE","body":"@divega \r\n> Re what you mention about creating temp tables and joining them with queries, it seems to me that for the scenarios in which this would actually help, e.g. there is a collection of values, TVPs on SQL Server would probably be a better option. For all other scenarios, if we just follow the same rules as the server, it seems that either inlining the function or using a simple parameter would be sufficient.\r\n\r\nWhy would a TVP on SQL Server be a better option for the EFCore Query generator?  With a TVP, you need to create a user-defined table type. Whose responsibility is that going to be, and why would EFCore want to take a dependency on whether the system catalog table sys.types has an appropriate TVP type mapping in place?\r\n\r\nThe scenario I am suggesting here is where you have a lot of values. If anything, you want to create a temp table with a clustered index so that you can join quickly. Currently, this is what we do with EFCore: We use EntityFramework.Extended and bulk insert into a non-temp table the id's we need to join on. Alternatively, we use .Batch() extension method and then Union in-memory all the separate batches. Either one will avoid a run-time error, but the pseudo-temp table approach is the most performant. The downside is that it requires two columns, a uniqueidentifier column and the actual id int column to populate in the select, so that the sub-select becomes `SELECT tx0.[Id] FROM [dbo].[PseudoTempTable] tx0 WHERE tx0.[Guid]='c65e4bea-fdbd-422a-adc9-ed962923f27b')` - and the downside to this is you now need some garbage collection process to make sure stuff gets cleaned up after the end of the connection.  Moreover, you've now reduced the index seek-ability because the primary key has to first be clustered on that uniqueidentifier. Bogus."}}
{"actor":{"display_login":"jzabroski"}}