This project is read-only.

Mapping Highlights


Mapping with Attributes

Entities are mapped to SQL statements via the "Column" attribute found in the `Marr.Data.Mapping` namespace. The ColumnAttribute contains any metadata needed to map the field or property to the database query.

If the SQL column or SP parameter name is the same as the property name, then you can simply add the Column attribute without specifying a name:

[Column]
public string Name { get; set; }


If they are different, then you can manually specify the column name:
 [Column("DESC")]
 public string Description { get; set; }

NOTE: Column attributes can also be added to fields or properties of all scopes (private, public, etc).

By default, the DataMapper will attempt to automatically detect the correct parameter data type given the .NET CLR data type and the current database provider being used, but you can also specify a specific datatype.

Fluent Mapping

You can also create mappings for an entity using the Marr.Data.Mapping.FluentMappings class.

NOTE: Although attribute mappings exist, the Fluent Mappings are the preferred mapping mechanism for a few reasons:
1) There are some powerful features that are only available via the Fluent Mappings (such as lazy loading, eager loading and the ToDB/FromDB property transform methods).
2) Adding mapping attributes litters your domain entities with data layer concerns, while the Fluent Mappings allows you to keep mapping in your data layer and out of your domain layer.
With that said, mapping attributes are still available and some people prefer them for their simplicity and convenience.

Consider the following entities:
namespace Marr.Data.UnitTests.Entities
{
    public class Person
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public DateTime BirthDate { get; set; }
        public bool IsHappy { get; set; }
        public List<Pet> Pets { get; set; }
    }

    public class Pet
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }
}


To map all public properties in an entity (that are not of type ICollection):
            var mappings = new FluentMappings();

            mappings
                .Entity<Person>()
                    .Table.MapTable("PersonTable")
                    .Columns.AutoMapSimpleTypeProperties()
                        .For(p => p.ID)
                            .SetPrimaryKey()
                            .SetReturnValue()
                            .SetAutoIncrement()
                    .Relationships.AutoMapICollectionOrComplexProperties()
                .Entity<Pet>()
                    .Columns.AutoMapSimpleTypeProperties()
                        .For(p => p.ID)
                            .SetPrimaryKey()
                            .SetAltName("Pet_ID")
                        .For(p => p.Name)
                            .SetAltName("Pet_Name");

NOTE: The "AutoMapSimpleTypeProperties" function will map all public properties (that are not of type ICollection) to columns with the exact same name. So this will map the a property named "ID" to a column named "ID". After calling this method, you can still manually call the ".For" method to set options for a specific property if need be. Here we are setting additional properties on the "ID" column by setting is as the primary key.

The "AutoMapICollectionOrComplexProperties" function maps will map all public properties that are not simple types as relationship mappings. Mapping properties as relationships in this way allows you to load that part of the object graph using a view or join table query.

To map only specific public properties, replace the "AutoMap..." functions with the "MapProperties" function:
            var mappings = new FluentMappings();

            mappings
                .Entity<Person>()
                    .Table.MapTable("PersonTable")
                    .Columns.MapProperties() // This lets you manually map only the properties that you want
                        .For(p => p.ID)
                            .SetPrimaryKey()
                            .SetReturnValue()
                            .SetAutoIncrement()
                        .For(p =>p.Name).SetColumnName("First_Name")
                        .For(p => p.Age)
                        .For(p => p.BirthDate)
                        .For(p => p.IsHappy)
                    .Relationships.MapProperties() // Again, lets you manually map only the properties you want
                        .For(p => p.Pets)

                .Entity<Pet>()
                    .Columns.MapProperties()
                        .For(p => p.ID)
                            .SetPrimaryKey()
                            .SetAltName("Pet_ID")
                        .For(p => p.Name);


The "MapProperties" function above requires you to explicitly specify every property that will be mapped to a DB column.

Apply Common Mappings to all descendants of a common base class

If you have a common base class for your entities, you can apply mappings to all sub-classes of the given base class.
For example, let's say we have the following base interface:
public interface IEntity
{
    int ID { get; set; }
}

And the following sub-classed entities:
public class Order : IEntity
{
    public int ID { get; set; }
    public DateTime OrderDate { get; set; }
    public List<OrderItem> OrderItems { get; set; }
    ....
}
public class OrderItem : IEntity
{
    public int ID { get; set; }
    public int OrderID { get; set; }
    public decimal Amount { get; set; }
    public int Qty {get; set; }
    ....
}


You could map columns using the FluentMappings class like this:
    new FluentMappings()		
	.ForEachEntity<IEntityBase>(entity => entity
	    .Columns.AutoMapSimpleTypeProperties()
	        .For(e => e.ID).SetPrimaryKey().SetAutoIncrement().SetReturnValue()
            .Tables.MapTable(type => type.Name + "s") // Pluralize tables
        )
        .Entity<OtherEntity()
        ...
        .Entity<YetAnotherEntity>()
        ...


You can

Massaging Data via Mappings

ToDB(...) / FromDB(...)

New in v.3.22, the ToDB and FromDB functions allow you to "massage" data for a specific column as data is mapped to that column to or from the database.
Example 1: Automatically setting a timestamp on a DateTime column
...
        .For(p => p.Modified)
            .ToDB(val => new DateTime.Now)
...


Example 2: Multiply an incoming int field value by 2
...
        .For(p => p.Amount)
            .FromDB(valFromDB => (valFromDB as int) * 2)
...


The 2nd example is a bit contrived, but this provides a way to intercept incoming data from the DB and modify it before it is pushed into the entity.

Example 3: Setting up reusable functions to handle conversions in mappings
	// DB to Model Conversions
	Func<object, object> nullToZero = obj => obj == DBNull.Value ? 0 : obj;
	Func<object, object> strToInt = objStr => Convert.ToInt32(nullToZero(objStr));

...
	.For(p => p.Age) // This is an int field
		.FromDB(strToInt)
...

Example 3 handles a scenario where your property is an int, but the database stores age as a string (for reasons beyond our control). Working in conjunction with each other, these functions allows you to automatically convert your DB string value to your int property.

AltName (for column aliases)

You can map an AltName to columns to avoid naming collisions if you are loading an object graph from a view. For example, if you have a view that loads people with their pets, since both Person and Pet entities have an "ID" column, you would need to use aliased column names in the view to separate them. So you might alias the Pet ID as "Pet_ID" in the view. To load the object graph using that view, you would need to specify an AltName for your Pet ID property:
                .Entity<Pet>()
                    .Columns.MapProperties()
                        .For(p => p.ID)
                            .SetPrimaryKey()
                            .SetAltName("Pet_ID")
                        .For(p => p.Name);


If you have complete over your views, I prefer the convention of prefixing all columns in a view with a table prefix. For example, your view query might look like this:
SELECT per.ID as per_ID, per.Name as per_Name, per.Age as per_Age, per.BirthDate as per_BirthDate, per.IsHappy as per_IsHappy, 
pet.ID as pet_ID, pet.Name as pet.Name
FROM Person per
INNER JOIN Pet pet ON pet.PersonID = per.ID


This convention would allow you to use the "PrefixAltNames" fluent method which prefixes all properties for the given entity:
            var mappings = new FluentMappings();

            mappings
                .Entity<Person>()
                    .Columns.AutoMapSimpleTypeProperties()
                        .PrefixAltNames("per_")
                        .For(p => p.ID).SetPrimaryKey().SetReturnValue().SetAutoIncrement()
                    .Relationships.AutoMapICollectionOrComplexProperties()
                .Entity<Pet>()
                    .Columns.AutoMapSimpleTypeProperties()
                        .PrefixAltNames("pet_")
                        .For(p => p.ID)
                            .SetPrimaryKey()
                        .For(p => p.Name);

Fluent Mapping (alternative method)

You can also create fluent mappings using the MapBuilder class.
(The FluentMappings class is actually a very thin wrapper over the MapBuilder classes that provides methods to easily create the most commonly used mappings.)
While the MapBuilder can be used to create the more common mappings, it can also be used to create more powerful custom convention based mappings.

Lazy Loading

As of v3.15, you can now designate fields to be lazy loaded. (Lazy loading is only available if you are using the fluent mapping methods, and as of now, is not available using attribute mapping.)

For this example, we will have a "Building" entity which can have many "Office" entities.
A simplified entity model would look like this:
    public class Building
    {
        // Creates a LazyLoaded proxy for a list of offices.
        private LazyLoaded<List<Office>> _offices;

        // NOTE: If you are using .NET 4.0, you can eliminate the dependeny on the LazyLoaded<T> 
        // class by declaring your lazy loaded field as a "dynamic":
        //private dynamic _offices; 

        public string Name { get; set; }
        public List<Office> Offices
        {
            get { return _offices; }
            set { _offices = value; }
        }
    }

    public class Office
    {
        public string BuildingName { get; set; }
        public int Number { get; set; }
    }



Note that we are mapping directly to the private backing field "_offices", which is of type LazyLoaded<List<Office>>. The LazyLoaded<T> is the proxy that will take care of loading the list of offices on an as-needed basis. LazyLoaded<List<Office>> implicitly converts to List<Office>, so our public Offices property can easily set and get the backing field.
If you are using .NET 3.5, you must declare your backing field to LazyLoaded<T>. However, if you are using .NET 4.0 and you do not want to directly reference the Marr.Data.Mapping namespace, you can declare your lazy loaded backing field as a "dynamic" type.

Next we need to declare the relationship from Building to Offices in our mappings as lazy loaded, and we will specify the query that will be used to load the list of offices. The LazyLoad fluent method takes a Func that has an IDataMapper and a TParent parameter (the parent entity is "Building" in this example), and returns a list of offices. In this case, we want to query all offices where BuildingName matches the parent building's Name:

        public void CreateMappings()
        {
            var map = new FluentMappings()
            map
                .Entity<Building>()
                    .Columns.AutoMapSimpleTypeProperties()
                        .PrefixAltNames("b")
                        .For(b.Name).SetPrimaryKey()
                    .Relationships.MapProperties()
                        // Note we are binding the lazy load proxy directly to the private backing field
                        .For("_offices") 
                            .LazyLoad((db, bldg) => 
                                db.Query<Office>().Where(ofc => ofc.BuildingName == bldg.Name).ToList())
                .Entity<Office>()
                    .Column.AutoMapSimpleTypeProperties();
        }


Now we can test that our list of offices is lazy loading (assuming our database tables are populated with test data):

        [TestMethod]
        public void TestLazyLoad()
        {
            var db = new DataMapper("...", "...");

            Building building = db.Query<Building>().Where(b => b.Name == "Building1").FirstOrDefault();

            Assert.IsTrue(building.Offices.Count > 0);
        }



Eager Loading using a Linq Query Expression

Eager loading a property will cause the DataMapper to generate a query to load that object at the same time that the parent object is loaded. Unlike the LazyLoaded example above, an eager loaded property does not require a proxy field in your entity. If we were to use eager loading on the example above instead of lazy loading, your entity model would look like this instead:

    public class Building
    {
        public string Name { get; set; }
        public List<Office> Offices { get; set; }
    }

    public class Office
    {
        public string BuildingName { get; set; }
        public int Number { get; set; }
    }


And the mapping would change to this:
        public void CreateMappings()
        {
            var map = new FluentMappings()
            map
                .Entity<Building>()
                    .Columns.AutoMapSimpleTypeProperties()
                        .For(b.Name).SetPrimaryKey()
                    .Relationships.MapProperties()
                        .For(b => b.Offices).EagerLoadMany(b => b.Name) // Foreign key mapping
                .Entity<Office>()
                    .Column.AutoMapSimpleTypeProperties();
        }


NOTE: Instead of using the foreign key mapping above, you can also create a Linq query expression that will execute when the mapping is loaded:
        public void CreateMappings()
        {
            var map = new FluentMappings()
            map
                .Entity<Building>()
                    .Columns.AutoMapSimpleTypeProperties()
                        .For(b.Name).SetPrimaryKey()
                    .Relationships.MapProperties()
                        .For(b => b.Offices)
                            .EagerLoad((db, bldg) => 
                                db.Query<Office>().Where(ofc => ofc.BuildingName == bldg.Name))
                .Entity<Office>()
                    .Column.AutoMapSimpleTypeProperties();
        }



Eager Loading using a Join Expression

It is also possible to specify an eager load in the FluentMappings using either the "JoinOne" or "JoinMany" methods. This will cause append the related child method to the parent by way of a JOIN statement in the resulting SQL query. (The default is a LEFT JOIN, but there is an overload that lets you change this).
Here is how the Eager Loaded example above would look if it was reworked as a join:
public void CreateMappings()
{
  var map = new FluentMappings()
  map
    .Entity<Building>()
      .Columns.AutoMapSimpleTypeProperties()
        .PrefixAltNames("b")
        .For(b.Name).SetPrimaryKey()
      .Relationships.MapProperties()
        .For(b => b.Offices)
          .JoinMany<Office>(bld => bld.Offices, (bld, ofc) => bld.Name == ofc.BuildingName)
      .Entity<Office>()
        .Column.AutoMapSimpleTypeProperties()
          .PrefixAltNames("o")
          .For(o => o.BuildingName).SetPrimaryKey()
          .For(o => o.Number).SetPrimaryKey
}


NOTE: Calling PrefixAltNames with different prefix strings on each of your entities that will be joined will cause the column aliases for each of the generated entity queries to be prefixed with whatever string you provide. This ensures that there will not be any naming collisions between entities if they have properties with the same name. Technically it doesn't matter here since neither of the entities in this example share any property names. However, it is a good practice to follow when using the JoinMany and JoinOne mappings to ensure that naming conflicts will always be avoided.

Fluent Mapping Example

Example of common mappings using the MapBuilder:
public void InitMappings()
        {
            MapBuilder builder = new MapBuilder();

            builder.BuildTable<Person>("PersonTable");

            builder.BuildColumns<Person>()
                .For(p => p.ID)
                    .SetPrimaryKey()
                    .SetReturnValue()
                    .SetAutoIncrement();

            builder.BuildRelationships<Person>();

            builder.BuildColumns<Pet>()
                .For(p => p.ID)
                    .SetPrimaryKey()
                    .SetAltName("Pet_ID")
                .For(p => p.Name)
                    .SetAltName("Pet_Name")
                    .SetSize(50);
        }



Creating Mappings using Conventions

Example of using convention based mappings feature to map only properties of type DateTime:
    var mapBuilder = new MapBuilder();
    mapBuilder.BuildColumns<Person>(m => 
        m.MemberType == MemberTypes.Property && 
        (m as PropertyInfo).PropertyType == typeof(DateTime));

Last edited Apr 27, 2015 at 5:59 AM by jmarr, version 20

Comments

No comments yet.