How do I make Dapper Dommel insert an application generated primary key?

Question

Suppose my entity is

public class AppUser
{
    string Id { get; set; }
    string Name { get; set; }
}

It looks like by default Dapper Dommel doesn't insert the Id field. It would generate SQL very close to this:

insert into AppUser (Name) values ("Dapper") select cast(scope_identity() as int)

This SQL is generated by using the Dapper Dommel Insert function like so:

using (var connection = new System.Data.SqlClient.SqlConnection(ConnectionString))
{
    connection.Open();
    connection.Insert<AppUser>(new User { Id = "someGuid", Name = "Dapper" });
}

I would like it to insert the Id column that I have provided a value for and also not perform the select cast(scope_identity() as int) query. That is, I would like something like this

insert into AppUser (Id, Name) values ("someGuid", "Dapper")

I can't seem to find this in the documentation. Does anyone know how to achieve this?

Answer 1

In preliminary testing this is working for me. Effectively this code is telling Dommel that none of your properties are generated/computed. You may need to be a bit more specific for your use case if you have calculated properties that you want ignored. I haven't needed that so far.

internal class KeysNotGeneratedPropertyResolver : DommelPropertyResolver
{
    public override IEnumerable<ColumnPropertyInfo> ResolveProperties(Type type)
    {
        return base.ResolveProperties(type)
            .Select(column => new ColumnPropertyInfo(column.Property, DatabaseGeneratedOption.None));
    }
}

internal class NotGeneratedKeyResolver : IKeyPropertyResolver
{
    private static readonly IKeyPropertyResolver DefaultResolver = new DommelKeyPropertyResolver();
    public ColumnPropertyInfo[] ResolveKeyProperties(Type type)
    {
        return DefaultResolver.ResolveKeyProperties(type)
            .Select(info => new ColumnPropertyInfo(info.Property, DatabaseGeneratedOption.None))
            .ToArray();
    }
}

Then in your configuration

 DommelMapper.SetPropertyResolver(new KeysNotGeneratedPropertyResolver());
 DommelMapper.SetKeyPropertyResolver(new NotGeneratedKeyResolver());

Answer 2

TL;DR;

Due to a bug in the library, the 'Id' property is considered as identity and excluded from the generated insert statement. Use Dapper-Dommel-FluentMap library with the files provided below and you should be fine.

Explanation:

I had the same issue with the Dapper-Dommel-FluentMap library(a complementary sister library providing more mapping functionalities) and I managed to find a solution after digging a bit(few hours) into its source code.

The reason for the Id property not getting inserted is because both Dommel and Dommel-FluentMap treat all key properties(or the ones named Id) as identities. However, in real life applications, not all key properties are identities, making this behavior a bug.

This was somewhat fixed and merged to the official GitHub repo in June 2021, but the author hasn't updated the NuGet package since 8/23/2020, questioning the reliability of this library. So, we are left with one choice if we want to continue using Dapper-Dommel: override the default property and key property resolvers with our own implementations.

Optionally, you can roll out your own custom implementation within the Dapper-Dommel library, but you are on your own there.

Solution

Begin with adding the Dapper.FluentMap.Dommel NuGet package.

After that, add the following files in your project:

public class CustomDommelPropertyResolver : DefaultPropertyResolver
{
    private static readonly IPropertyResolver DefaultResolver = new DefaultPropertyResolver();

    /// <inheritdoc/>
    protected override IEnumerable<PropertyInfo> FilterComplexTypes(IEnumerable<PropertyInfo> properties)
    {
        foreach (var propertyInfo in properties)
        {
            var type = propertyInfo.PropertyType;
            type = Nullable.GetUnderlyingType(type) ?? type;

            if (type.GetTypeInfo().IsPrimitive || type.GetTypeInfo().IsEnum || PrimitiveTypes.Contains(type))
            {
                yield return propertyInfo;
            }
        }
    }

    /// <inheritdoc/>
    public override IEnumerable<ColumnPropertyInfo> ResolveProperties(Type type)
    {
        IEntityMap entityMap;
        if (FluentMapper.EntityMaps.TryGetValue(type, out entityMap))
        {
            foreach (var property in FilterComplexTypes(type.GetProperties()))
            {
                // Determine whether the property should be ignored.
                var propertyMap = entityMap.PropertyMaps.FirstOrDefault(p => p.PropertyInfo.Name == property.Name);
                if (propertyMap == null || !propertyMap.Ignored)
                {
                    var dommelPropertyMap = propertyMap as DommelPropertyMap;
                    if (dommelPropertyMap != null)
                    {
                        yield return new ColumnPropertyInfo(property, dommelPropertyMap.GeneratedOption != default
                                                                    ? dommelPropertyMap.GeneratedOption
                                                                    : (dommelPropertyMap.Identity ? DatabaseGeneratedOption.Identity : DatabaseGeneratedOption.None));
                    }
                    else
                    {
                        yield return new ColumnPropertyInfo(property);
                    }
                }
            }
        }
        else
        {
            foreach (var property in DefaultResolver.ResolveProperties(type))
            {
                yield return property;
            }
        }
    }
}

and

public class CustomDommelKeyPropertyResolver : IKeyPropertyResolver
{
    private static readonly IKeyPropertyResolver DefaultResolver = new DefaultKeyPropertyResolver();

    /// <inheritdoc/>
    public ColumnPropertyInfo[] ResolveKeyProperties(Type type)
    {
        IEntityMap entityMap;
        if (!FluentMapper.EntityMaps.TryGetValue(type, out entityMap))
        {
            return DefaultResolver.ResolveKeyProperties(type);
        }

        var mapping = entityMap as IDommelEntityMap;
        if (mapping != null)
        {
            var allPropertyMaps = entityMap.PropertyMaps.OfType<DommelPropertyMap>();
            var keyPropertyInfos = allPropertyMaps
                 .Where(e => e.Key)
                 .Select(x => new ColumnPropertyInfo(x.PropertyInfo, x.GeneratedOption != default 
                                                                   ? x.GeneratedOption
                                                                   : (x.Identity ? DatabaseGeneratedOption.Identity : DatabaseGeneratedOption.None)))
                 .ToArray();

            // Now make sure there aren't any missing key properties that weren't explicitly defined in the mapping.
            try
            {
                // Make sure to exclude any keys that were defined in the dommel entity map and not marked as keys.
                var defaultKeyPropertyInfos = DefaultResolver.ResolveKeyProperties(type).Where(x => allPropertyMaps.Count(y => y.PropertyInfo.Equals(x.Property)) == 0);
                keyPropertyInfos = keyPropertyInfos.Union(defaultKeyPropertyInfos).ToArray();
            }
            catch
            {
                // There could be no default Ids found. This is okay as long as we found a custom one.
                if (keyPropertyInfos.Length == 0)
                {
                    throw new InvalidOperationException($"Could not find the key properties for type '{type.FullName}'.");
                }
            }

            return keyPropertyInfos;
        }

        // Fall back to the default mapping strategy.
        return DefaultResolver.ResolveKeyProperties(type);
    }
}

Add the following AppUser Dommel map:

public class AppUserMap : DommelEntityMap<AppUser>
{
    public AppUserMap()
    {
        Map(p => p.Id).ToColumn("Id")
            .IsKey()
            .SetGeneratedOption(DatabaseGeneratedOption.None)
            .Identity = false; // explicitly specified for clarity

        Map(x => x.Name).ToColumn("Name");
    }
}

Add the map in the Dommel's FluentMapper initialize method:

FluentMapper.Initialize(config =>
{
    config.AddMap(new AppUserMap());

    config.ForDommel();
});

Make sure you override the Dommel's default resolvers afterwards:

DommelMapper.SetKeyPropertyResolver(new CustomDommelKeyPropertyResolver());
DommelMapper.SetPropertyResolver(new CustomDommelPropertyResolver());

With this in place, unless you explicitly specify the property's database generated option or identity, they will not get picked as identities.

The code that fixes this bug is the following:

.Select(x => new ColumnPropertyInfo(x.PropertyInfo, x.GeneratedOption != default 
                                                                   ? x.GeneratedOption
                                                                   : (x.Identity ? DatabaseGeneratedOption.Identity : DatabaseGeneratedOption.None)))
 .ToArray();