npgsql
Translate hstore access to SQL
Is there a way to query hstore columns using LINQ ? I would expect something like this to work:
public class Product
{
public int Id { get; set; }
//This is correctly set as hstore in OnModelCreating
public Dictionary<string, string> Tags { get; set; }
}
ApplicationDbContext db = ...
var prods = db.Products.Where(p => p.Tags["Variant"].Equals("SD")).ToList();
However that WHERE statement is evaluated at client and I want it to be evaluated in db server.
I expect something like this to be generated
select * from "Products" where "Tags" -> 'Variant' = 'SD'
This works so everything is configured correctly.
var prods = db.Products.FromSql("select * from \"Products\" where \"Tags\" -> 'Variant' = 'SD'").ToList();
It's definitely possible to perform this kind of translation to SQL, but this isn't currently supported. Since the translation doesn't exist at the moment, the above code will trigger client evaluation - EF Core will select everything and perform the Where() client-side.
I did something similar for translating basic .NET Regex functionality to PostgreSQL regex operators (see #6), also see #4 which would translate JSON operators.
I'll keep this open as a feature request.
Would this be difficult to implement ? I have never done anything like this but I would like to look into it. Could you point me in some direction ?
@DavizOWNS while translating expressions to SQL is usually simple (and even fun), unfortunately this case doesn't involve simple method call translation (like, say, regular expressions) but the indexing operator.
A while back I did some work in a branch on #120, which involves something similar but for an array. Let me bring that branch up to date and see if I can even merge it (or some of it) for 2.0.0. After that I'll come back here and try to give some guidance.
@DavizOWNS you may want to take a look at https://github.com/aspnet/EntityFramework/issues/9183, which is me asking for a review from the EF Core team on work I did to translate array operations (including subscripting). You would probably do something not too far off, so you may want to look at my work, and let's wait for their feedback.
FYI the array operation translation work has been merged for 2.0.0. You may wish to take a look at b79fd0cf4f308656e1b40a8dfd1f8638263efed7 to see how that was done and start working from there.
Just dropping by my solution in case anyone else is struggling with this. First, I created the following function on my database:
CREATE OR REPLACE FUNCTION public.hstore_has_key_value(IN "@hstore" hstore, IN "@key" text, IN "@operator" text, IN "@value" text)
RETURNS boolean
AS $func$
SELECT
CASE
WHEN "@operator" = '=' THEN "@hstore" -> "@key" = "@value"
WHEN "@operator" = '<>' THEN "@hstore" -> "@key" <> "@value"
WHEN "@operator" = '>' THEN "@hstore" -> "@key" > "@value"
WHEN "@operator" = '>=' THEN "@hstore" -> "@key" >= "@value"
WHEN "@operator" = '<' THEN "@hstore" -> "@key" < "@value"
WHEN "@operator" = '<=' THEN "@hstore" -> "@key" <= "@value"
WHEN "@operator" = 'LIKE' THEN "@hstore" -> "@key" LIKE "@value"
ELSE "@hstore" -> "@key" = "@value"
END
$func$ LANGUAGE sql;
Then a dummy DbFunction on my C# code:
public static class DbFunctionsExtensions
{
public static bool HasKeyValue(Dictionary<string, string> dic, string key, string @operator, string value)
{
throw new NotImplementedException("For use only as an EF core Db function");
}
}
Configured the mapping between the functions on the DbContext:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//other stuff
modelBuilder.HasDbFunction(typeof(DbFunctionsExtensions).GetMethods()
.Where(m =>
{
return m.Name == "HasKeyValue";
})
.SingleOrDefault())
.HasName("public.hstore_has_key_value");
}
Use it and be happy:
var result = dbContext.Entites.AsNoTracking().Where(r => DbFunctionsExtensions.HasKeyValue(r.Dictionary, "someKey", "=", "someValue")).FirstOrDefault();
I have implemented full support for hstore based querying. I know it has been a little while :-)
You can now do all the above queries you've suggested as well as the following methods are all supported:
- Dictionary<string, string>.Item (i.e. myDictionary["key"])
- Dictionary<string, string>.Item
- Dictionary<string, string>.ContainsKey
- ImmutableDictionary<string, string>.ContainsKey
- Dictionary<string, string>.ContainsValue
- ImmutableDictionary<string, string>.ContainsValue
- Dictionary<string, string>.Count
- ImmutableDictionary<string, string>.Count
- ImmutableDictionary<string, string>.IsEmpty
- Enumerable.Any
If you're going to use the Where(myDictionary => myDictionary["key"] == myValue), you should probably do a Where(myDictionary => myDictionary.ContainsKey("key") && myDictionary["key"] == myValue) instead. Since in .NET accessing the item at an index throws an exception, however technically when this evaluates on the SQL server, because PostgreSQL does not, it won't actually throw an exception.
I have implemented full support for
hstorebased querying. I know it has been a little while :-)
Great! Does this enable ORDER BY over hstore values too?
Hi,
Should @yinzara changes also work for a "Converted Dictionary" in EF 9.0.2?
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.2"/>
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="9.0.2"/>
For example:
public enum MyEnum
{
Key1,
Key2
}
public class DictionaryMyEnumStringToDictionaryStringStringConverter()
: ValueConverter<Dictionary<MyEnum, string>, Dictionary<string, string>>(
v => v.ToDictionary(k => k.Key.ToString(), v => v.Value),
v => v.ToDictionary(k => Enum.Parse<MyEnum>(k.Key), v => v.Value));
Then, in OnModelCreating I would have:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<MyEntity>(entity =>
{
entity.Property(e => e.MyIntStringDictionary)
.HasConversion(new DictionaryMyEnumStringToDictionaryStringStringConverter());
});
}
When executing the following LINQ:
var fetchedData = _context.MyEntity
.FirstOrDefault(x => x.MyIntStringDictionary.ContainsKey(MyEnum.Key1)
&& x.MyIntStringDictionary[MyEnum.Key1] == "someValue");
I'm getting a The LINQ expression 'DbSet<MyEntity>() .Where(m => m.MyIntStringDictionary.ContainsKey(Key1) && m.MyIntStringDictionary.get_Item(Key1) == "someValue")' could not be translated exception.
I can write a simple runnable solution, if needed. Thank you in advance.
right, sorry, my fault. I should have checked the PR's status before adding the comment.
Just in case anyone else is in the same situation, as a workaround (until the PR is merged, hopefully), I managed to use FromSqlRaw.
I.e.
context.MyEntity
.FromSqlRaw($"SELECT * FROM MyEntity WHERE MyIntStringDictionary -> '{enumKey}' = '{value}'")
.FirstOrDefault();
Hope you'll get a chance to look at yinzara's changes, roji, and that changes are okay to be merged and released soon.
Really appreciate your work. Thanks
