EF Core Support Improvements in dotConnect for PostgreSQL 7.21

The new version of Devart dotConnect for PostgreSQL includes significantly improved support for Entity Framework Core. It both supports the new data types and extends LINQ query translation capabilities. Additionally, we have improved Entity Framework Core Code-First Migrations support.

WHERE Condition Support for Index in Code-First Migrations

For Entity Framework Core 3 and 5, dotConnect for PostgreSQL now supports specifying a condition for an index. This allows creating PostgreSQL partial indexes.

Fluent mapping code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Table>()
        .HasIndex(t => t.NumericColumn)
        .HasFilter("\"NumericColumn\" < 1000");
}

Code-First Migration code:

  migrationBuilder.CreateIndex(name: "IX_Table_NumericColumn", table: "Table", column: "NumericColumn", filter: "\"NumericColumn\" < 1000");

SQL DDL:

CREATE INDEX "IX_Table_NumericColumn" ON "Table" ("NumericColumn") WHERE "NumericColumn" < 1000

LINQ to Entities Improvements

dotConnect for PostgreSQL now supports translation of the following LINQ features to SQL for both EF Core 3 and EF Core 5:

• The static IsNullOrWhiteSpace() method of the String class
• The static Today property and instance DayOfWeek and Ticks properties of the DateTime class
• The following static methods of the Math class: Max(), Min(), Sqrt(), Log(), Log10(), Sin(), Cos(), Tan(), Asin(), Acos(), Atan()

For EF Core 5, more LINQ features can be translated to SQL:

• The static Parse() method of the System.Net.IPAddress class
• The static Parse() method of the System.Net.NetworkInformation.PhysicalAddress class
• The following static methods of the MathF class: Abs(), Round(), Truncate(), Floor(), Ceiling(), Max(), Min(), Pow(), Sqrt(), Log(), Log10(), Sin(), Cos(), Tan(), Asin(), Acos(), Atan()

Uri Data Type Mapping

For Entity Framework Core 3 and 5, dotConnect for PostgreSQL now supports mapping the internet/intranet System.Uri type to PostgreSQL ‘text’ data type.

public class Blog {
  public int Id { get; set; }
  public Uri Url { get; set; }
  public List<Post> Posts { get; set; }
}

CREATE TABLE "Blog" ( 
  "Id" serial NOT NULL,
  "Url" text NULL,
  PRIMARY KEY ("Id")
)

IPAddress and PhysicalAddress data type mapping

For Entity Framework Core 5, dotConnect for PostgreSQL now supports mapping network types System.Net.IPAddress and System.Net.NetworkInformation.PhysicalAddress to PostgreSQL ‘inet’ and ‘macaddr’/’macaddr8’ data types.

public class AccessLog {
  public int Id { get; set; }
  public Uri Url { get; set; }
  public IPAddress IP { get; set; }
  public DateTime Timestamp { get; set; }
}

CREATE TABLE "AccessLog" ( 
  "Id" serial NOT NULL,
  "Url" text NULL,
  "IP" inet NULL,
  "Timestamp" text NULL,
  PRIMARY KEY ("Id")
)

Dictionary data type mapping

For Entity Framework Core 3 and 5, dotConnect for PostgreSQL now supports mapping the dictionary .NET types to HSTORE PostgreSQL data type.

Mapping of the following types is supported:

•  Dictionary<TKey, TValue>
•  SortedDictionary<TKey, TValue>
•  ImmutableDictionary<TKey, TValue>
•  ImmutableSortedDictionary<TKey, TValue>

The most natural approach would be mapping string to string, i.e. Dictionary<string, string>, SortedDictionary<string, string>, ImmutableDictionary<string, string>, or ImmutableSortedDictionary<string, string>, because PostgreSQL HSTORE data type stores and returns data as a collection of key/value pairs of strings. key must be not null, but value can be null.

public class HstoreSample {
  public int Id { get; set; }
  public Dictionary<string, string> Dictionary { get; set; }
  public ImmutableDictionary<string, string> ImmutableDictionary { get; set; }
}

CREATE TABLE "HstoreSample" ( 
  "Id" serial NOT NULL,
  "Dictionary" hstore NULL,
  "ImmutableDictionary" hstore NULL,
  PRIMARY KEY ("Id")
)

Sometimes using non-string types can be necessary, so we have supported a number of primitive types as generic type arguments. The following .NET types can be used as both key and value:

•  String
•  Byte
•  SByte
•  Int16
•  Int32
•  Int64
•  Single
•  Double
•  Decimal
•  DateTime
•  DateTimeOffset
•  Boolean

Any TKey/TValue combinations are supported. You can use Dictionary<int, string>, SortedDictionary<string, bool>, ImmutableDictionary<DateTime, decimal> or any others. It’s not recommended, however, to use floating-point numeric types, like Single and Double, as a key, because of their imprecision. Nullable versions of these data types (int?, DateTime?, etc.) are not supported.

LINQ queries support the following features for dictionaries:

1) Getting a value by the key

The following LINQ query:

var query = context.HstoreSample.Where(t => t.Dictionary["a"] == "first").ToList();

results in the following SQL:

SELECT * FROM "HstoreSample" WHERE "Dictionary" -> 'a' = 'first'

2) Check for the presence of a key in the dictionary

LINQ:

var query = context.HstoreSample.Where(t => t.Dictionary.ContainsKey("a")).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE "Dictionary" ? 'a'

3) Check if the key/value pair is present in the dictionary (only for ImmutableDictionary and ImmutableSortedDictionary)

LINQ:

var query = context.HstoreSample.Where(t => t.ImmutableDictionary.Contains("a", "first")).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE "ImmutableDictionary" @> hstore('a', 'first')

4) Getting the number of key/value pairs in the dictionary

LINQ:

var query = context.HstoreSample.Where(t => t.Dictionary.Count <= 3).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE ARRAY_LENGTH(AKEYS("Dictionary"), 1) <= 3

5) Check if the dictionary is empty (only for ImmutableDictionary and ImmutableSortedDictionary)

LINQ:

var query = context.HstoreSample.Where(t => !t.ImmutableDictionary.IsEmpty).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE NOT (ARRAY_LENGTH(AKEYS("ImmutableDictionary"), 1) = 0)

6) Concatenation of HSTORE values (only for ImmutableDictionary and ImmutableSortedDictionary)

LINQ:

var query = context.HstoreSample.Where(t => t.ImmutableDictionary.AddRange(t.AnotherDictionary).ContainsKey("a")).ToList();

SQL:

SELECT * FROM "HstoreSample" t WHERE "ImmutableDictionary" || "AnotherDictionary” ? 'a'

7) Conversion of a string to a dictionary:

If a string column, parameter, or variable stores a valid HSTORE value, it can be converted to HSTORE, and you can perform the corresponding operations on it.

Such value can be converted to any of the supported types –  Dictionary/SortedDictionary/ImmutableDictionary/ImmutableSortedDictionary.

LINQ:

var query = context.TextTable.Where(t => ((Dictionary<string, string>)(object)t.TextColumn).ContainsKey("a")).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE CAST("TextColumn" AS hstore) ? 'a'

8) Conversion of Dictionary and SortedDictionary to ImmutableDictionary and ImmutableSortedDictionary

The following methods are available: .ToImmutableDictionary() and .ToImmutableSortedDictionary().

For example, let’s convert a Dictionary to ImmutableDictionary type in order to use the .Contains() method:

LINQ:

var query = context.HstoreSample.Where(t => t.Dictionary.ToImmutableDictionary().Contains("a", "first")).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE "Dictionary" @> hstore('a', 'first')

The following example converts Dictionary to ImmutableSortedDictionary in order to perform a comparison by value with an ImmutableSortedDictionary instance:

LINQ:

var dictionary = new Dictionary<string, string>() { { "a", "first" }, { "c", "3rd" }, { "b", "second" } }.ToImmutableSortedDictionary();
var query = context.HstoreSample.Where(t => t.Dictionary.ToImmutableSortedDictionary() == dictionary).ToList();

SQL:

SELECT * FROM "HstoreSample" WHERE "Dictionary" = :p__dictionary_0

Conclusion

We are glad to bring you the updated dotConnect for PostgreSQL with the new features, and we don’t plan to stop. Support of HSTORE mapping to Dictionary/SortedDictionary/ImmutableDictionary/ImmutableSortedDictionary can be improved further depending on your feedback and suggestions. We are also going to extend support for mapping more .NET types to get the most from the huge PostgreSQL data type variety. Code-First Migrations has a potential of improvement too. Besides, we keep support of Entity Framework Core 6 with its new features and new .NET 6 types, like DateOnly and TimeOnly, our highest priority task.