Skip to content

Instantly share code, notes, and snippets.

@SafeerH
Created June 16, 2022 17:17
Show Gist options
  • Save SafeerH/2271a0f50a1c1214e22064cacfd77e99 to your computer and use it in GitHub Desktop.
Save SafeerH/2271a0f50a1c1214e22064cacfd77e99 to your computer and use it in GitHub Desktop.
.NET 6 / EF Core - Add IPAddress DbFunctions (works with Npgsql 6 - PostgreSQL)
public static class IPAddressDbFunctions
{
/// <summary>
/// Translates to (in PostgreSQL):
/// <code>
/// ('[address]'::inet &amp; '[subnetMask]'::inet)
/// </code>
/// </summary>
// ('[address]'::inet & '[subnetMask]'::inet)
public static IPAddress GetNetworkIP(IPAddress address, IPAddress subnetMask) => throw new NotSupportedException();
/// <summary>
/// Translates to (in PostgreSQL):
/// <code>
/// ('[address1]'::inet &amp; '[subnetMask]'::inet) = ('[address2]'::inet &amp; '[subnetMask]'::inet)
/// </code>
/// </summary>
// ('[address1]'::inet & '[subnetMask]'::inet) = ('[address2]'::inet & '[subnetMask]'::inet)
public static bool AreInSameNetwork(IPAddress address1, IPAddress address2, IPAddress subnetMask) => throw new NotSupportedException();
}
public static partial class ModelBuilderExtensions
{
public static void ConfigureIPAddressDbFunctions(this ModelBuilder modelBuilder)
{
// Ref: https://docs.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping#mapping-a-method-to-a-custom-sql
static SqlExpression GetNetworkIPSqlExpression(SqlExpression address, SqlExpression subnet)
{
return new SqlBinaryExpression(
ExpressionType.And,
address,
subnet,
address.Type,
address.TypeMapping
);
}
modelBuilder
.HasDbFunction(typeof(IPAddressDbFunctions).GetMethod(
nameof(IPAddressDbFunctions.GetNetworkIP),
new[] { typeof(IPAddress), typeof(IPAddress) })!
)
.HasTranslation(args => GetNetworkIPSqlExpression(args[0], args[1]));
modelBuilder
.HasDbFunction(typeof(IPAddressDbFunctions).GetMethod(
nameof(IPAddressDbFunctions.AreInSameNetwork),
new[] { typeof(IPAddress), typeof(IPAddress), typeof(IPAddress) })!
)
.HasTranslation(
args => new SqlBinaryExpression(
ExpressionType.Equal,
GetNetworkIPSqlExpression(args[0], args[2]),
GetNetworkIPSqlExpression(args[1], args[2]),
args[0].Type,
args[0].TypeMapping
)
);
}
}
public class MyDbContext : DbContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// ...
modelBuilder.ConfigureIPAddressDbFunctions();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment