More information on Relationships

Dec 23, 2012 at 4:59 AM
Edited Dec 23, 2012 at 5:04 AM

Hi,

I'm trying to insert an Entity with 3 one-to-one relationships, but I keep getting an error:

No mapping exists from DbType PageScore.Core.Domain.Category to a known SqlCeType.

My domain model is as follows:

    public interface IEntity
    {
        int Id { get; set; }
        Guid Guid { get; set; }
    }
    public abstract class Entity
    {
        protected Entity()
        {
            (this as IEntity).Guid = Guid.NewGuid();
        }
    }
    public class Judge : Entity, IEntity
    {
        public int Id { get; set; }
        public Guid Guid { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
    }
    public class Category : Entity, IEntity
    {
        public int Id { get; set; }
        public Guid Guid { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public decimal MinScore { get; set; }
        public decimal MaxScore { get; set; }
    }
    public class Contestant : Entity, IEntity
    {
        public Contestant()
        {
            this.Scores = new List<Score>();
        }

        public int Id { get; set; }
        public Guid Guid { get; set; }
        public string Name { get; set; }
        public DateTime DOB { get; set; }
        public string Email { get; set; }
        public List<Score> Scores { get; set; }
    }
    public class Score : Entity, IEntity
    {
        public int Id { get; set; }
        public Guid Guid { get; set; }
        public decimal Value { get; set; }
        public Category Category { get; set; }
        public Judge Judge { get; set; }
        public Contestant Contestant { get; set; }
    }

 

** By the way, it would be great if you allowed entity inheritance so I wouldn't have to implement this hacky work-around interface. Inheritance is a problem with the MapBuilder because the relfected BindingFlags do not walk up the inheritance chain.

Anyhow, and my mappings are as follows:

 

            var mb = new MapBuilder();

            mb.BuildTable<Judge>( "tblJudge" );
            mb.BuildColumns<Judge>()
                .For( j => j.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue();

            mb.BuildTable<Category>( "tblCategory" );
            mb.BuildColumns<Category>()
                .For( c => c.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue();
            mb.BuildRelationships<Category>();

            mb.BuildTable<Contestant>( "tblContestant" );
            mb.BuildColumns<Contestant>()
                .For( c => c.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue();
            mb.BuildRelationships<Contestant>()
                .For( x => x.Scores )
                .SetOneToMany();

            mb.BuildTable<Score>( "tblScore" );
            mb.BuildColumns<Score>()
                .For( s => s.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue();
            mb.BuildRelationships<Score>()
                .For( x => x.Category )
                .SetOneToOne();

 

Note: I only tried to map one one-to-one property on the "Score" class "Score.Category"  relationship for testing, to see how relationships worked and if I could get rid of the exception. But for some reason, Marr isn't picking up the relationship.

Let me know if I'm doing something wrong. I'll continue to trace through the source code.

The code that's causing the exception:

 

            var score = new Score()
                {
                    Contestant = this.contestant,
                    Category = category,
                    Judge = judge,
                    Value = value
                };

            AddScore( score );

 

 

        public static void AddScore(Score s)
        {
            using( var dm = Session())
            {
                dm.Insert( s );
            }
        }
        

        public static DataMapper Session()
        {
            var dm = new DataMapper( System.Data.SqlServerCe.SqlCeProviderFactory.Instance, @"data source=|DataDirectory|\DB.sdf" )
                {
                    SqlMode = SqlModes.Text
                };
            return dm;
        }
Coordinator
Dec 23, 2012 at 6:08 PM
Edited Dec 23, 2012 at 6:12 PM

The MapBuilder should traverse through the inheritance chain, so you should be able to get rid of the interface.  The problem is that you are using "mb.BuildColumns" method, which will automatically grab all properties of all types (including properties of complex types that should be mapped as relationships).  If you want to use "BuildColumns", the approach is to assume that it will grab all columns, and then you manually ignore your complex properties (relationship entities).  Here are the reworked mappings using this approach:

 

public void InitMappings()
        {
            var mb = new MapBuilder();

            mb.BuildTable<Entities.bchavez.Judge>("tblJudge");
            mb.BuildColumns<Entities.bchavez.Judge>()
                .For(j => j.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue();

            mb.BuildTable<Entities.bchavez.Category>("tblCategory");
            mb.BuildColumns<Entities.bchavez.Category>()
                .For(c => c.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue();
            mb.BuildTable<Entities.bchavez.Contestant>("tblContestant");
            mb.BuildColumns<Entities.bchavez.Contestant>()
                .For(c => c.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue()
                .Ignore(c => c.Scores);
            mb.BuildRelationships<Entities.bchavez.Contestant>();

            mb.BuildTable<Entities.bchavez.Score>("tblScore");
            mb.BuildColumns<Entities.bchavez.Score>()
                .For(s => s.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue()
                .Ignore(s => s.Judge)
                .Ignore(s => s.Contestant)
                .Ignore(s => s.Category);
            mb.BuildRelationships<Entities.bchavez.Score>();
        }

 

The next approach is to use "mb.Columns" instead of "BuildColumns"  With this approach the MapBuilder will not try to add any columns on it own, so you can explicitly add only the columns you want using the ".For(m => m.Id)" syntax.  This gives you the most fine grained control of your mappings, and is very straight forward.  What you see is what you get.

The third approach is the us "mb>BuildColumnsFromSimpleTypes" method, which is a smarter combination of the above two.  With this approach, the MapBuilder will automatically add columns with primitive types, datetime, string, decimal, enums.  It will ignore other types (Lists<T> and non-primitive types which will usually be used for your relationships).  Here are the reworked mappings for this approach:

public void InitMappings()
        {
            var mb = new MapBuilder();

            mb.BuildTable("tblJudge");
            mb.BuildColumnsFromSimpleTypes()
                .For(j => j.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue()
                .For(s => s.Guid);

            mb.BuildTable("tblCategory");
            mb.BuildColumnsFromSimpleTypes()
                .For(c => c.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue()
                .For(s => s.Guid);
            mb.BuildTable("tblContestant");
            mb.BuildColumnsFromSimpleTypes()
                .For(c => c.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue()
                .For(s => s.Guid);
            mb.BuildRelationships();

            mb.BuildTable("tblScore");
            mb.BuildColumnsFromSimpleTypes()
                .For(s => s.Id)
                    .SetPrimaryKey()
                    .SetAutoIncrement()
                    .SetReturnValue()
                .For(s => s.Guid);
            mb.BuildRelationships();
        }

 

One potential bug that I just noticed is that "Guid" is not properly detected as a "simple type".  This is a change that I should probably make.  But you can still add it manually if you want to use this method by adding .For(s => s.Guid) to each entity's column mappings.

 

 

Coordinator
Dec 23, 2012 at 6:10 PM

And here are your updated entities (without the interface):

    public abstract class Entity
    {
        public int Id { get; set; }
        public Guid Guid { get; set; }

        protected Entity()
        {
            this.Guid = Guid.NewGuid();
        }
    }

    public class Judge : Entity
    {
        public string Name { get; set; }
        public string Email { get; set; }
    }

    public class Category : Entity
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public decimal MinScore { get; set; }
        public decimal MaxScore { get; set; }
    }
    public class Contestant : Entity
    {
        public Contestant()
        {
            this.Scores = new List();
        }

        public string Name { get; set; }
        public DateTime DOB { get; set; }
        public string Email { get; set; }
        public List Scores { get; set; }
    }

    public class Score : Entity
    {
        public decimal Value { get; set; }
        public Category Category { get; set; }
        public Judge Judge { get; set; }
        public Contestant Contestant { get; set; }
    }
Dec 23, 2012 at 8:27 PM
Edited Dec 23, 2012 at 9:43 PM

Hi Jordan,

Thanks for the fixes, I'll study them and see if they work!

I don't know if you're open to some feedback or observations, but here are my thoughts:

The naming convention for the MapBuilder API has things like BuildTable, BuildColumns, almost sounds like MapBuilder actually building the SQL tables. If you have a rather long Init() sequence, it could get confusing. Or even to a greenhorn programmer could be thrown off by a call to BuildTable().

Perhaps a different naming convention would be something like:

MapTable, MapFields, MapProeprties, Map... etc. AutoMapFields, ManualMap, OverrideMap

To me, this makes more sense because essentially we're still mapping properties to columns. I think better naming that is naturally consistent with the MapBuilder class could help prevent misuse of the API similar to what I had done. I don't think I was gorking any boundary differences in behavior that MapBuilder could provide when mapping entities.

One Example:

BuildColumnsFromSimpleTypes()

AutoMapColumnsForSimpleTypes()

Or even

AutoMapPropertiesForNativeTypes()

--

         //Example 1  
mb.AutoMapPropertiesForNativeTypes< Score >() .OverrideFor(c => c.Id) .SetPrimaryKey() .SetAutoIncrement() .SetReturnValue()
or
//Example 2
mb.AutoMap< Score >("tblScore")
.MapNativePropertyTypes()
.OverrideFor(c => c.Id)
.SetPrimaryKey()
.SetAutoIncrement()
.SetReturnValue()
.MapRelationships()

Another suggestion: I think setting the return value on Id should be done by default; instead of having explicitly set it. Would this be consistent with most ORMs that do this by default?

Explicitly "Override" kind-of implies you're over-ridding a mapping rule that was done by the previous call. I think that seems natural. Personally, I like Example 2 the best because it's concise, and allows me to maintain the mapping in a single fluent line (and scope) instead of breaking them up into something like BuildTable, then BuildColumn. Calling BuildTable then BuildColumns seemed odd to me at first when was using the API. Having something like Example 2 would clear things up a bit, and allow you to separate the different kinds of valid API mapping calls for manual & auto mapping.

Let me know your thoughts. But still, you've done a great job creating a simple light-weight ORM for simple/easy projects, instead of having to implement a full-blown ORM like NHibernate for projects that don't really need NH.

Thanks again for your help.
Brian

Dec 23, 2012 at 8:44 PM
Edited Dec 23, 2012 at 8:47 PM

I've updated the domain model, with inheritance, and using the BuildColumnsFromSimpleTypes() method of mapping, and when I delete a judge, I get the following exception:

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 132:            using( var dm = Session())
Line 133:            {
Line 134:                dm.Delete<Judge>( x => x.Guid == guid );
Line 135:            }
Line 136:        }


Source File: c:\Code\Projects\PagentScore\Source\PageScore.Core\Dao.cs    Line: 134

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
   Marr.Data.DataHelper.GetColumnName(MemberInfo member, Boolean useAltName) +89
   Marr.Data.QGen.WhereBuilder`1.GetFullyQualifiedColumnName(MemberInfo member) +111
   Marr.Data.QGen.WhereBuilder`1.VisitMemberAccess(MemberExpression expression) +43
   Marr.Data.QGen.ExpressionVisitor.Visit(Expression expression) +282
   Marr.Data.QGen.WhereBuilder`1.VisitBinary(BinaryExpression expression) +45
   Marr.Data.QGen.ExpressionVisitor.Visit(Expression expression) +165
   Marr.Data.QGen.ExpressionVisitor.VisitLamda(LambdaExpression lambdaExpression) +16
   Marr.Data.QGen.ExpressionVisitor.Visit(Expression expression) +84
   Marr.Data.QGen.WhereBuilder`1..ctor(DbCommand command, Dialect dialect, Expression filter, TableCollection tables, Boolean useAltName, Boolean tablePrefix) +211
   Marr.Data.DataMapper.Delete(String tableName, Expression`1 filter) +313
   Marr.Data.DataMapper.Delete(Expression`1 filter) +47
   PageScore.Core.Dao.DeleteJudge(Guid guid) in c:\Code\Projects\PagentScore\Source\PageScore.Core\Dao.cs:134
   PagentScore.Web.Judges2.cmdDeleteJudge_OnClick(Object sender, CommandEventArgs e) in c:\Code\Projects\PagentScore\Source\PagentScore.Web\Judges.aspx.cs:47
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +9553498
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +159
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

 

I think this was the main reason for me moving to an Interface based domain model for the Guids and Ids; because somewhere deep inside the ORM it wasn't picking up the inherited properties.

Init:

 

            var mb = new MapBuilder();

            mb.BuildTable<Judge>( "tblJudge" );
            mb.BuildColumnsFromSimpleTypes<Judge>()
                .For( j => j.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue()
                .For( j => j.Guid );

            mb.BuildTable<Category>( "tblCategory" );
            mb.BuildColumnsFromSimpleTypes<Category>()
                .For( c => c.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue()
                .For( c => c.Guid );

            mb.BuildTable<Contestant>( "tblContestant" );
            mb.BuildColumnsFromSimpleTypes<Contestant>()
                .For( c => c.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue()
                .For( c => c.Guid );
            mb.BuildRelationships<Contestant>();

            mb.BuildTable<Score>( "tblScore" );
            mb.BuildColumnsFromSimpleTypes<Score>()
                .For( s => s.Id )
                .SetPrimaryKey()
                .SetAutoIncrement()
                .SetReturnValue();
            mb.BuildRelationships<Score>();

 

And Domain:

 

    public abstract class Entity
    {
        public int Id { get; set; }
        public Guid Guid { get; set; }

        protected Entity()
        {
            this.Guid = Guid.NewGuid();
        }
    }
    public class Judge : Entity
    {
        public string Name { get; set; }
        public string Email { get; set; }
    }
    public class Category : Entity
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public decimal MinScore { get; set; }
        public decimal MaxScore { get; set; }
    }
    public class Contestant : Entity
    {
        public Contestant()
        {
            this.Scores = new List<Score>();
        }
        public string Name { get; set; }
        public DateTime DOB { get; set; }
        public string Email { get; set; }
        public List<Score> Scores { get; set; }
    }
    public class Score : Entity
    {
        public decimal Value { get; set; }
        public Category Category { get; set; }
        public Judge Judge { get; set; }
        public Contestant Contestant { get; set; }
    }
Coordinator
Dec 23, 2012 at 9:37 PM

That is a bug!  Fix to come shortly. 

I may also do the rename you mentioned while I'm at it.

Coordinator
Dec 23, 2012 at 10:45 PM
Edited Dec 23, 2012 at 10:45 PM

Hang in there.. the fix has been checked in, and now I am going to add the new MapBuilder methods and then leave the old ones as deprecated.

Coordinator
Dec 23, 2012 at 11:14 PM

The hotfix is in v3.16, which has been uploaded to Codeplex. 

I have not renamed the MapBuilder methods yet, but I will do that later, and then I will upload to Nuget.

 

Dec 24, 2012 at 12:31 AM

Great, Thank you Jordan. I'll give it a try!

Dec 24, 2012 at 3:28 AM

Oh thanks so much! The delete works! :) :) :)

Dec 24, 2012 at 3:48 AM
Edited Dec 24, 2012 at 4:13 AM

Hey Jordan,

When I insert a Score with 3 one-to-one references (Judge, Contestant, Category), I get an Marr generates an Insert statement similar to:

==== Begin Query Trace ====

QUERY TYPE:
Text

QUERY TEXT:
INSERT INTO [tblScore] ([Value]) VALUES (@Value)

PARAMETERS:
Value = [5]

==== End Query Trace ====

I have 3 foreign keys in tblScore (JudgeId, ContestantId, and CategoryId), who's columns are marked as NotNull. Inserting with the above SQL that the ORM generates violates the NotNull foreign key constraint.

 

The column cannot contain null values. [ Column name = JudgeId,Table name = tblScore ]

 

Any ideas on how to make the ORM include the identity ID values with the SQL insert? The Judge, Contestant, and Category identities are non-zero upon a Score insert.

I'm using the following relationship:

 

            mb.BuildRelationships<Score>()

 

and I've tried:

 

            mb.BuildRelationships()
                .For( s => s.Category )
                .SetOneToOne()
                .For( s => s.Contestant )
                .SetOneToOne()
                .For( s => s.Judge)
                .SetOneToOne();

Both result in generating the Score insert without identity values for the foreign keys. Perhaps I just need to relax the NotNull constraint?

 

-Brian 

Coordinator
Dec 24, 2012 at 8:53 AM
Edited Dec 24, 2012 at 8:58 AM

I think you should keep the NotNull constraint in the DB, and then add CategoryId, JudgeId, and ContestantId properties to your Score entity. 

When you query your Score entity, it will populate these foreign key Id fields along with the relationships.  Then your Score object will have everything it needs to do an insert or update.

if you are using one of the "auto" mapping methods, this won't require any changes to your MapBuilder calls to handle the added properties.

 

Coordinator
Dec 31, 2012 at 12:14 AM
Edited Dec 31, 2012 at 2:38 AM

Btw, the latest version (3.17) has a new FluentMappings class that implements most of your suggestions. 

UPDATE: It is now uploaded to Nuget as well.