Translate hstore access to SQL

[davidkvc]

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();

[roji]

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.

[davidkvc]

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 ?

[roji]

@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.

[roji]

@DavizOWNS you may want to take a look at dotnet/efcore#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.

[roji]

FYI the array operation translation work has been merged for 2.0.0. You may wish to take a look at b79fd0c to see how that was done and start working from there.

[davidkvc]

Created PR #240 with my first attempt at this.

[rafaelvascc]

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();

[yinzara]

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.

[MatteoSp]

I have implemented full support for hstore based querying. I know it has been a little while :-)

Great! Does this enable ORDER BY over hstore values too?

[roji]

It should - let us know if it doesn't.