V2: [Column] and [Table] Attributes

@mgravell

This would be part of the overall Dapper v2 project (#688)

There's obviously been a lot of interest in controlling column and table name mappings in a coherent and fully featured way. But all of the PRs we've seen are all over the map as to how to go about this. They're also Dapper.Contrib restricted, whereas we need something to cover things overall. For example calling .Insert<T>() and then calling .Query<T> afterwards and that not working isn't expected or awesome.

Let me preface this. Both of these are optional. They work together, but are 100% optional - if you use neither then Dapper's existing behavior works as-is with mappings. The proposal is also for both, not an either/or. See below for how they work together:

Basically, this can't be Dapper.Contrib, it needs to be global. This type, this member goes to this table, this column...in a controllable way. I'm picturing an API like this:

[Table("Users")]
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Column("Address")]
    public string AddressSomethingCrazy { get; set; }
}

Now, this doesn't work for everyone. You can't add attributes everywhere and some people don't want to. They want conventions, or covering types they don't control - so we need something else. I'm not sure what these are called, but basically we make the type and member to table and column mappings pluggable. For example, something like this:

namespace Dapper
{
    public partial class Options
    {
        public Func<Type, string> TableNameMapper { get; set; }
        public Func<Type, string> DefaultTableNameMapper { get; }
        public Func<Type, PropertyInfo, string> ColumnNameMapper { get; set; }
        public Func<Type, PropertyInfo, string> DefaultColumnNameMapper { get; }
    }
}

So if your convention is init caps, underscores, emoji, or whatever really, you could define this in one place. You could call the default mapper as a fallback in your own function as well, if you just want to handle a few cases and fall back for the rest. Or, you could make your own attributes, and entirely base all mappings on whatever scheme you could come up with. As a simple example:

SqlMapper.TableNameMapper = type =>
{
	if (type == typeof(User)) return "Users";
	return SqlMapper.DefaultTableNameMapper(type);
};

Note: these work together, the Options.DefaultTableNameMapper and Options.DefaultColumnNameMapper would look at the attributes in their implementation. So this isn't an either/or proposal, the two approaches collaborate to handle all the cases we've seen thus far, while being totally optional all together.

All of these would apply across the board, to .Query<T>, .Insert<T, TKey>, .Update<T>, .Get<T>, and...well, you get the idea. They need to work everywhere, that's why it's needs to be in Dapper core and consumed all the way through .Contrib, etc.

Now, that deals with naming (I hope) - but there's more we can do here too. For example, is there interest in controlling defaults or something else in attributes? Naming is the biggest thing I've seen by far, but there may be some other areas we can solve along the same lines.

One prime example here is [Column(PrimaryKey = true)] (of which there could be many), and possibly some others along those lines.

Some other problems the above potentially solves (or not) would be case sensitivity (or not) and being something that the mutation methods (like .Insert()) can use. For example, whether to quote a field or not across .Contrib provider implementations when generating T-SQL would be important. Perhaps Column has a CaseSensitive property...or something else. The two-way nature on the deserialization of that is a thing, but generally handled by fallbacks today. Or it could be handled by exact names in the column attribute or Func overrides...or any of the combination of those things. Lots of options here - what makes sense to expose in a simple way for lots of use cases?

Related Info

Related issues: #447, #463, #515, #605, #623, #670, #681, #572
Potentially covered: #482, #571

Thoughts? Suggestions? Things you'd like to throw in my general direction?

cc @mgravell

Attributes Vs Config...

I really think Dapper should only support 1 convention, even if there's a group of people who don't like Attributes or prefer attributes and don't like Config.

Dapper is a beautiful tiny lightweight ORM that does the bare minimum. Yes it needs some mapping to get around legacy and poorly named tables/columns in databases, and vice versa. But not at the sacrifice of bloating Dapper.

/my 2 cents

I'm impartial to Attributes vs Config. As long as Dapper doesn't lose focus and become bloat.

@NickCraver

I appreciate your attention to this @NickCraver! Hopefully there is a good solution to cover the vast majority of issues/preferences.

I highly prefer config (not attributes) based setup because I may not own the class/model, the other big ones are conventions. Working with legacy schema (I can't change) where all the tables are prefixed like tb_TableName or the columns are chrColumnName 🤢

I prefer my database identity columns to be in the form of TableNameID, and my c# classes as just ID. I would like the flexibility to setup a convention to map ID to [ClassName]Id and just reuse it for all of my queries and stored procs.

Thanks!

I hope that the Dapper is supported in two ways, and Attribute is simple and intuitive. But I think Dapper in complex scenes require enhanced Mapper function, especially in the one-to-many, many-to-many scenario, EF-like configuration method based on Lambda expression expression ability.

A suggestion for the Attributes vs Config discussion:

Go with the Config option, and supply an implementation (in Dapper.Contrib?) which would do attribute mapping. That way by default Dapper would remain small, but if Attribute mapping is required, add a reference to Dapper.Contrib. You could even supply a .UseAttributeMapping extension which would apply all of the Func<Type...> required in one go if needs be.

Personal bias is towards Config rather than Attributes, but I could live with either. Config feels more flexible however.

@Pondidum

+1 for @Pondidum. The Config is a must for the Dapper Core, the Attributes are for Dapper.Contrib only (optional). Since I use Dapper.Contrib I will use the Attributes - and I would like to have an option to change/enhance the Attributes by the Config.

Just a minor note: you should decide, what will happen, when TableNameMapper, ColumnNameMapper return null or empty string - either fall back to the default mappers or throw an exception.

@Gonkers

@Gonkers another option there is a virtual method on the attribute and subclass to change default behavior; worth consideration

@phillip-haydon

I'm seeing a lot of surprising replies, that's awesome. Let me make sure we're on the same page when discussing first:

The proposal isn't for an either/or, it's for both. But they wouldn't be 2 separate approaches, they work together. For example, SqlMapper. DefaultTableNameMapper would look for the [Table] attribute and use it if present, otherwise it'd do what we do today. The same holds for SqlMapper. DefaultColumnNameMapper, it'd look for ColumnAttribute if present, and default to the current behavior if not.

You'd free to use the attributes or not. You'd be free to use none of this and get the exact same behavior we have today.

Is that what everyone understood from the proposal? If not I'll do some editing to clarify.

@phillip-haydon I'm not sure I understand the bloat comments here - what we're talking about is very minimal and removes several one-off options both in the project and proposed today - they could instead be included mappers...or the other mappers could be in another project, or something else. Can you explain a bit more? Or did I botch mentioning the totally optional part?

@Gonkers same thing - did you read this as either/or - or would you only want to see 1 for some reason?

@uliian I don't think we'll be tackling 1:many here, that's a very different generation and consumption problem, likely in Dapper.Contrib for the foreseeable future...but all together another feature entirely.

@Pondidum Same question - was the either/or and not both confusing, or does your opinion stay the same regardless?

@xmedeko Both are optional...same question, confusing on the proposal?

It looks like I need to edit the copy to clarify some things, will wait for responses there. Also related proposal: I think we should move all of these static settings to another place, probably:

namespace Dapper
{
    public static class Options
    {
        public Func<Type, string> TableNameMapper { get; set; }
        public Func<Type, string> DefaultTableNameMapper { get; }
        public Func<Type, PropertyInfo, string> ColumnNameMapper { get; set; }
        public Func<Type, PropertyInfo, string> DefaultColumnNameMapper { get; }
     }   
}

...along with all other options in V2, so they're contained in an intuitive spot. This would make access more intuitive, like this:

Dapper.Options.TableNameMapper = ...;

Thoughts?

Yep, we are on the same page. Yes, all is optional for the user. By optional I was thinking something else, never mind about it.

You can make just:

public Func<Type, string> TableNameMapper { get; set; }
public Func<Type, PropertyInfo, string> ColumnNameMapper { get; set; }

And the user should do the mapper chaining:

var origMapper = Options.TableNameMapper;
Options.TableNameMapper = (t, s) => { 
    if (type == typeof(User)) return "Users";
    return origMapper(type);
}

The Caliburn.Micro project does config the same way.
Dapper.Contrib may just plug in own mappers to provide Attribute mappings.

@NickCraver

@NickCraver - For conn.Query("select ...", ...), is it possible to name the "tables" in the split? spliton: "commentid, followerid", splitto: "Comment, User". Also, with conn.QueryMultiple, maybe a way to map result sets to "Table" using ordinal in the set - "Blog,Post,Comment,User" where each is a table in the result set. Maybe getting closer to other features, but related to table mapping nonetheless.

@xmedeko

@xmedeko I think so many people have asked for this type of mapping, it simply no longer belongs in .Contrib, it's something we have to take on and maintain. It also means it can work by default, which is easier for the user. Without that, I think we get a lot of people that forget Options.TableNameMapper = .... and Options.ColumnNameMapper = ... and wonder why their attributes aren't working everywhere.

It also means core things like type mapping conversions could never be on those attributes (e.g. handling GUID <> byte[]) and such across providers. There so much extensibility we can do if core is aware of these. That's the rationale for splitting them into .Contrib, given they'd be completely optional to use?

@philn5d That's done via the generic args on the multi-map overloads, e.g. .Query<Blog, Post, Comment, User, Blog>(), so I'm not sure where string names would come into play, can you clarify a bit? Or do we just suck at making people aware the multi-map functionality exists? I want to do some Jekyll docs with v2, so please, let us have it on where the docs suck.

I think I misunderstood the 1/both/many part - suggested implementation so they work together is fine.
I would only be marginally concerned with bloat, but it seems like it'll be pretty minor either way.

I just updated the issue to clarify the confusing points, hopefully a bit better for getting on the same page.

@NickCraver

@NickCraver I don't think using Options as static is a good thing.

Someone might need to connect to two different databases at the same time using two different mapping schemes, with static that would require using AppDomains.

Examples:

A migration tool for old and new database schemes.
Storing customer data on one database and global application on another.
Converting a server database to SQLite to be sent to mobile users.

@Flash3001

@Flash3001 - What's the alternative, passing the options as an overload to every single mapper? Note this affects the deserializer cache, etc. Not making them static (as all options are today) would be a huge burden to take on, for a very small subset of cases. As an example, none of the examples you're mentioning would be practically supported today, since type mappers and such are global/static today.

Unless there's a way I can't think of to share this without passing it around everywhere, forking the cache, etc. - I just can't see us supporting those use cases in Dapper, just as we don't really today.

The "Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true" is already working for insert and update in Dapper.Contrib?

So there is no way to map properties to their columns using Dapper.Contrib, but why does the Get work and the Insert doesn't?

I‘m using Dapper's native Insert method, but the [Write(false)] attribute under Dapper.Contrib is not correctly recognized. If the author wants to update the attribute tag, it is recommended to use [NotMapped], because many projects are compatible with this tag, and the namespace is :System.ComponentModel.DataAnnotations.Schema.

It is insane that it's going on 5 years that Dapper.Contrib hasn't implemented a Property to Column mapping configuration. Creating an ORM, you would think that would be a 1.0 piece of functionality, or a quick 1.1 follow up. Without this functionality, using Dapper.Contrib in any sort of a decoupled way is impossible unless your models exactly match the database schema. I was able to use the TableNameMapper to decouple Model to Table.

It's also annoying that Dapper doesn't support or respect System.ComponentModel.DataAnnotations.Schema attributes. That would also go a long way to decouple your models and code from Dapper.

I guess Dapper's real goal is to make the framework so coupling that you'd never think of moving away from it.

It is open source, the original developers clearly do not need it, consider offering a commit!

Mike, I understand the point you're making. But we haven't even gotten a signal of consensus from the maintainers as to what they'd accept in a PR, and they're now working on V3 when this would be a PR for V1.

Of course the devs don't need it - this is their own DB ORM so they're fine with any coupling that matches their exact DB object naming conventions.

I think it was a pretty significant oversight all things considered from the tool, but as the thread has seen others have basically remade .contrib functionality to work without this coupling. Fingers crossed something along these lines makes it into V3.

@Ryanman

@Ryanman my thoughts exactly. I have to get some code done and in prod, but once I catch a slight break I'll probably be looking for another ORM that would allow that kind of decoupling. It's a shame because Dapper is a decent, lightweight framework, but I can't make that type of coupling choice in an enterprise application.

@mmulhearn

@mmulhearn , I have an ORM that uses attributes from DataAnnotations that allows for the decoupling you desire. https://github.com/dallasbeek/Dapper.Database

Maybe not ideal for your situation, but you could leverage a tool like AutoMapper to map your domain classes to your data classes. Obviously this is not quite as efficient because it involves an intermediate copy, but it would get the job done.

@pchasco

@pchasco I do use AutoMapper for class to class translations/mapping. However, the issue is pulling the data directly from the database into a class

@pchasco

@pchasco I do use AutoMapper for class to class translations/mapping. However, the issue is pulling the data directly from the database into a class

What I mean is to construct your classes to exactly model your database schema, without any column remapping. Then use AutoMapper between your data and domain layer to perform this mapping. As stated, not ideal because this is actually exactly one of the responsibilities of ORM, but it would work. Then again, employing a second mapping phase with AutoMapper with the wasted cycles during copy and wasted allocations may moot any benefit to using a lightweight ORM such as dapper to begin with...

If all you need is aliasing a property so Dapper.Contrib's InsertAsync() can insert it into the correct column, I found a way to do it:

Decorate the column in question with Dapper.Contrib's Computed attribute. This prevents InsertAsync() from inserting the property.
Add a read-only property to the class that returns the value of the original property.

I know this is ugly, and works best if the class in question is a DTO to be discarded after the INSERT but it works.

Here is a small implementation code that is working with v2 for both "properties" and "fields" column mappings:

public class CustomFieldPropertyTypeMap : SqlMapper.ITypeMap {
         private readonly Type type;
         private readonly Lazy<Dictionary<string, PropertyInfo>> propertyCacheLazy;
         private readonly Lazy<Dictionary<string, FieldInfo>> fieldCacheLazy;
         private readonly Lazy<Dictionary<string, ConstructorInfo>> constructorCacheLazy;
 
         public CustomFieldPropertyTypeMap(Type type, Dictionary<string, PropertyInfo> propertyCache, Dictionary<string, FieldInfo> fieldCache) {
             this.type = type;
 
             propertyCacheLazy = new Lazy<Dictionary<string, PropertyInfo>>(() => propertyCache);
             fieldCacheLazy = new Lazy<Dictionary<string, FieldInfo>>(() => fieldCache);
             constructorCacheLazy = new Lazy<Dictionary<string, ConstructorInfo>>(cacheConstructors);
         }
 
         private Dictionary<string, PropertyInfo> PropertyCache => propertyCacheLazy.Value;
         private Dictionary<string, FieldInfo> FieldCache => fieldCacheLazy.Value;
         private Dictionary<string, ConstructorInfo> ConstructorCache => constructorCacheLazy.Value;
 
         // Cache constructors based on parameter names
         private Dictionary<string, ConstructorInfo> cacheConstructors() {
             var cache = new Dictionary<string, ConstructorInfo>();
             foreach (var constructor in type.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)) {
                 var parameterNames = string.Join(",", constructor.GetParameters().Select(p => p.Name));
                 cache[parameterNames] = constructor;
             }
             return cache;
         }
 
         public ConstructorInfo FindConstructor(string[] names, Type[] types) {
             var key = string.Join(",", names);
             if (ConstructorCache.TryGetValue(key, out var constructor)) {
                 return constructor;
             }
 
             return type.GetConstructor(Type.EmptyTypes); // Fallback if no match found
         }
 
         public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName) {
             if (PropertyCache.TryGetValue(columnName, out var property)) {
                 return new PropertyMemberMap(columnName, property);
             }
 
             if (FieldCache.TryGetValue(columnName, out var field)) {
                 return new FieldMemberMap(columnName, field);
             }
 
             return null;
         }
 
         public SqlMapper.IMemberMap GetMember(string columnName) {
             if (PropertyCache.TryGetValue(columnName, out var property)) {
                 return new PropertyMemberMap(columnName, property);
             }
 
             if (FieldCache.TryGetValue(columnName, out var field)) {
                 return new FieldMemberMap(columnName, field);
             }
 
             return null;
         }
 
         public ConstructorInfo FindExplicitConstructor() {
             ConstructorInfo explicitConstructor = null;
             int maxParameters = -1;
 
             foreach (var constructor in type.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)) {
                 var parameters = constructor.GetParameters();
 
                 if (parameters.Length > maxParameters) {
                     maxParameters = parameters.Length;
                     explicitConstructor = constructor;
                 }
             }
 
             return explicitConstructor;
         }
 
         // Static methods to register type maps
         public static void register(Assembly assembly) {
             Type[] types = assembly.GetTypes();
 
             foreach (Type type in types) {
                 registerType(type);
             }
         }
 
         public static void registerInParallel(Assembly assembly) {
             Type[] types = assembly.GetTypes();
 
             Parallel.ForEach(types, type => registerType(type));
         }
 
         public static void registerType(Type type) {
             if (type.IsClass && type.GetCustomAttribute<TableAttribute>() != null) {
                 var propertyCache = new Dictionary<string, PropertyInfo>();
                 var fieldCache = new Dictionary<string, FieldInfo>();
 
                 PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
                 FieldInfo[] fields = type.GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
 
                 bool hasColumnAttribute = false;
 
                 foreach (var property in properties) {
                     var columnAttribute = property.GetCustomAttribute<ColumnAttribute>();
                     if (columnAttribute != null) {
                         propertyCache[columnAttribute.name] = property;
                         hasColumnAttribute = true;
                     }
                 }
 
                 foreach (var field in fields) {
                     var columnAttribute = field.GetCustomAttribute<ColumnAttribute>();
                     if (columnAttribute != null) {
                         fieldCache[columnAttribute.name] = field;
                         hasColumnAttribute = true;
                     }
                 }
 
                 if (hasColumnAttribute) {
                     SqlMapper.SetTypeMap(type, new CustomFieldPropertyTypeMap(type, propertyCache, fieldCache));
                 }
             }
         }
     }
 
 
 
 
 
     struct PropertyMemberMap : SqlMapper.IMemberMap {
         private readonly string columnName;
         private readonly PropertyInfo property;
         private readonly Func<object, object> getter;
         private readonly Action<object, object> setter;
 
         public PropertyMemberMap(string columnName, PropertyInfo property) {
             this.columnName = columnName;
             this.property = property;
             getter = (Func<object, object>)Delegate.CreateDelegate(typeof(Func<object, object>), property.GetGetMethod());
             setter = (Action<object, object>)Delegate.CreateDelegate(typeof(Action<object, object>), property.GetSetMethod());
         }
 
         public string ColumnName => columnName;
         public Type MemberType => property.PropertyType;
         public PropertyInfo Property => property;
         public FieldInfo Field => null;
         public ParameterInfo Parameter => null;
 
         public void SetValue(object obj, object value) {
             setter(obj, value);
         }
 
         public object GetValue(object obj) {
             return getter(obj);
         }
     }
 
 
 
 
     struct FieldMemberMap : SqlMapper.IMemberMap {
         private readonly string columnName;
         private readonly FieldInfo field;
         private readonly Func<object, object> getter;
         private readonly Action<object, object> setter;
 
         public FieldMemberMap(string columnName, FieldInfo field) {
             this.columnName = columnName;
             this.field = field;
             getter = (Func<object, object>)Delegate.CreateDelegate(typeof(Func<object, object>), field, "GetValue", ignoreCase: false);
             setter = (Action<object, object>)Delegate.CreateDelegate(typeof(Action<object, object>), field, "SetValue", ignoreCase: false);
         }
 
         public string ColumnName => columnName;
         public Type MemberType => field.FieldType;
         public PropertyInfo Property => null;
         public FieldInfo Field => field;
         public ParameterInfo Parameter => null;
 
         public void SetValue(object obj, object value) {
             setter(obj, value);
         }
 
         public object GetValue(object obj) {
             return getter(obj);
         }
     }

This can be sticked to any your custom attributes "ColumnAttribute" and "TableAttribute" or create those 2 attributes on your own.

To enable the support just register it:
CustomFieldPropertyTypeMap.register(assembly);

@NickCraver

@NickCraver @mgravell It seems there was a lot of excitement with the initial idea.
I, for one, come to this topic every few years I start a project with existing db column names that are painfully inconsistent.

Is there any plan to include Attribute column mapping? I am ready to contribute code as well if the idea is still acceptable.

I am increasingly of the mind that V2===AOT. By which I mean:

we already have vanilla/original Dapper
we already have DapperAOT that supports these things already (see https://github.com/DapperLib/DapperAOT/blob/main/src/Dapper.AOT/DbValueAttribute.cs#L22)

Adding a third runtime/ref-emit Dapper API might be overkill. I wonder if we can instead work more towards helping AOT fill in any feature gaps. A fast way to do this might be to add a generalized way to say "use this materializer for this type", which would fill in any remaining gaps.

Navigation Menu

Repository navigation

V2: [Column] and [Table] Attributes #722

Related Info

57 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

V2: [Column] and [Table] Attributes #722

Description

Related Info

Activity

phillip-haydon commented on Mar 14, 2017

Gonkers commented on Mar 14, 2017

uliian commented on Mar 14, 2017

Pondidum commented on Mar 14, 2017

xmedeko commented on Mar 14, 2017

mgravell commented on Mar 14, 2017

NickCraver commented on Mar 14, 2017

xmedeko commented on Mar 14, 2017

philn5d commented on Mar 14, 2017

NickCraver commented on Mar 14, 2017

Pondidum commented on Mar 14, 2017

NickCraver commented on Mar 14, 2017

Flash3001 commented on Mar 14, 2017

NickCraver commented on Mar 14, 2017

57 remaining items

vitornat commented on Jan 23, 2021

emaborsa commented on Jun 16, 2021

xingwen1987 commented on Jul 27, 2021

mmulhearn commented on Jan 18, 2022

mikebeaton commented on Jan 18, 2022

Ryanman commented on Jan 18, 2022

mmulhearn commented on Jan 18, 2022

dallasbeek commented on Jan 18, 2022

pchasco commented on Dec 23, 2022

mmulhearn commented on Jan 3, 2023

pchasco commented on Jan 3, 2023

lukacsandras-betsson commented on Nov 30, 2023

maksym-bandurko commented on Sep 4, 2024

fadiprocco commented on Jan 12, 2025

mgravell commented on Jan 12, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions