You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Reacted by Austin Drenski, Dmytro Shunkov, Jeremy Cook, Georg Jung, cailenmusselman, Mihai Sandulescu, Johann Schön, bestserg, dimitrov9, David Edey and 33 more
@toddams this is in the backlog, so there aren't immediate plans to tackle this... But it's definitely something I'd like to do at some point. More people upvoting this could help prioritize it, and of course anyone is free to work on it.
Note that ideally we'd do this both for inline collections (where the new array expression is inside the query) and for parameter collections. The latter is a bit tricky in terms of the type of the array element being used, and how we match its properties to the Contains item.
Also, check which databases support WHERE (1,2) IN ((3, 4), (5, 6)) syntax.. IIRC most/all do except for SQL Server, if that's the case we should do something at the EF level. Relevant issue there: dotnet/efcore#11799,
For anyone else having the same issue, this is a possible work around. However, please do note that this method is not really sufficient for all scenarios and should be thoroughly tested before using it.
publicstaticclassDatabaseFacadeExtensions{privatestaticreadonlyConcurrentDictionary<Type,string>DatabaseTypeNames=[];staticDatabaseFacadeExtensions(){// Initially populate the global type mapper as described in https://github.com/npgsql/npgsql/issues/5508#issuecomment-1872212391._=newNpgsqlSlimDataSourceBuilder();}publicstaticIQueryable<T>FromCollection<T>(thisDatabaseFacadedatabase,ICollection<T>collection)=>FromCollection(database,collection,serializerOptions:null);publicstaticIQueryable<T>FromCollection<T>(thisDatabaseFacadedatabase,ICollection<T>collection,JsonSerializerOptions?serializerOptions=null){if(!database.IsNpgsql())thrownewNotSupportedException("Only Npgsql is supported.");/* * EF-Core currently does not support generation of IN expressions for tuples https://github.com/npgsql/efcore.pg/issues/898 * This workaround passes the collection as json to the database which we can then use to create our temporary inline table. * * Note: Sort order of the JsonProperties and the columns in the query must match. * This is by default given as described in * - https://learn.microsoft.com/en-us/dotnet/api/system.type.getproperties * - https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/customize-properties */varjson=JsonSerializer.Serialize(collection,serializerOptions);varcolumns=typeof(T).GetProperties().Select(p =>$"\"{p.Name}\"{GetDbTypeName(p.PropertyType)}");
#pragma warning disable EF1002// Risk of vulnerability to SQL injection.varquery=database.SqlQueryRaw<T>($$""" SELECT * FROM jsonb_to_recordset({0}::jsonb) AS item({{string.Join(", ",columns)}})""",parameters:json);
#pragma warning restore EF1002// Risk of vulnerability to SQL injection.returnquery;}privatestaticstringGetDbTypeName(Typetype){varname=DatabaseTypeNames.GetOrAdd(type,static type =>{// We are querying the database type name by abusing the NpgsqlParameter and its DataTypeName. However, it only works if the value provided is not null.type=Nullable.GetUnderlyingType(type)??type;vardefaultValue=type.IsValueType?Activator.CreateInstance(type):string.Empty;varparameterType=typeof(NpgsqlParameter<>).MakeGenericType([type]);varparameter=Activator.CreateInstance(parameterType,args:[string.Empty,defaultValue])!;varname=((NpgsqlParameter)parameter).DataTypeName??thrownewInvalidOperationException($"The database type name could not be resolved for '{type}'.");returnname;});returnname;}}
This can then be used like seen blow:
varblogsSearch=new[]{newBlog{Id=1,Type=1},newBlog{Id=2,Type=1}};varblogs=(fromsearchinctx.Datanase.FromCollection(blogsSearch)
join bloginctx.Blogs on new{search.Id,search.Type} equals new{blog.Id,blog.Type}selectblog).ToList();
Activity
smitpatel commentedon Jun 4, 2019
This could work by overriding VisitNew in SqlTranslator and returning appropriate bracket notation printing expression and compose on top of that.
toddams commentedon Jul 29, 2020
Is there any plan for this feature?
roji commentedon Jul 29, 2020
@toddams this is in the backlog, so there aren't immediate plans to tackle this... But it's definitely something I'd like to do at some point. More people upvoting this could help prioritize it, and of course anyone is free to work on it.
roji commentedon Oct 6, 2023
Note that ideally we'd do this both for inline collections (where the new array expression is inside the query) and for parameter collections. The latter is a bit tricky in terms of the type of the array element being used, and how we match its properties to the Contains item.
Also, check which databases support
WHERE (1,2) IN ((3, 4), (5, 6))syntax.. IIRC most/all do except for SQL Server, if that's the case we should do something at the EF level. Relevant issue there: dotnet/efcore#11799,malix0 commentedon Oct 6, 2023
I add my vote to implement this feature.
It will be critical to query the partitioned tables to properly access the data using the partitioned indexes.
TwentyFourMinutes commentedon Jan 7, 2025
For anyone else having the same issue, this is a possible work around. However, please do note that this method is not really sufficient for all scenarios and should be thoroughly tested before using it.
This can then be used like seen blow: