CompositeId() throws exception - Could not compile the mapping document: (XmlDocument)

After defining a map for composite key

 CompositeId()
                .KeyProperty(x => x.title)
                .KeyProperty(x => x.label);

The following exception is raised when trying to create a composite key

CreateSessionFactory() - Inner exception: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.
..
CreateSessionFactory() - Inner exception: Could not compile the mapping document: (XmlDocument)
CreateSessionFactory() - Inner exception: composite-id class must override Equals(): CSharpTechies.Data.Entities.post

This means "post" entity definition should override at least 2 members GetHashCode() and Equals(object obj) Now here is how code snippet looks

public override int GetHashCode()
{
 int hashCode = 0;
 hashCode = hashCode ^ title.GetHashCode() ^ label.GetHashCode();
 return hashCode;
}

public override bool Equals(object obj)
{
 var toCompare = obj as post;
 if (toCompare == null)
 {
  return false;
 }
 return (this.GetHashCode() != toCompare.GetHashCode());
} 

Remember if the entity "post" is referenced elsewhere (ie.. foreign key) then you might get some more exceptions

CreateSessionFactory() - caught EXCEPTION: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.
..
CreateSessionFactory() - Inner exception: Foreign key (FK36342426872A80CB:AuthorPosts [Post_id])) must have same number of columns as the referenced primary key (Post[title, label])

So update reference mapping as

  References(x => x.Post)
                .Columns(new string[] { "title", "label" })
                .Not.Update()
                .Not.Insert();

or one-to-many mapping as

 HasMany(x => x.Posts)
                .KeyColumns.Add("title")
                .KeyColumns.Add("label")
                .Not.LazyLoad()
                .Inverse();

“We have to stop optimizing for programmers and start optimizing for users.” – Jeff Atwood

Continue Reading

Consuming return value from stored procedure using NHibernate without mapping

After haggling around I have found a solution that works. First I have created a stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     RETURN @TOTALINCOMECOUNT;
END;

This is a simple stored procedure that just returns a value. Now lets look at NHibernate code block that can execute the stored procedure and read the return value.

using (var session = OpenSession())
{
     var query = session.CreateSQLQuery("EXEC GETTOTALINCOME");
     object result = query.UniqueResult();
     retVal = Convert.ToInt64(result);
}

I am getting exceptions so I started debugging it and have found "result" is null. I have looked at some articles of using stored procedure, but I do not want to define mapping either FulentNHibernate mapped classes or .hbm configuration.

I have looked again into stored procedure definition, NHibernate can read the values that are returned by a "SELECT" statement but not "RETURN". So I have changed my stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     SELECT @TOTALINCOMECOUNT;
END;

Now this works.

“We have to stop optimizing for programmers and start optimizing for users.” – Jeff Atwood

Continue Reading

Using join on a subquery with NHibernate

NHibernate is one of the best ORM solution available. That said sometimes its not easy to find a way around your problem. I have been trying to create a join on a subquery and want to uses "EXISTS" on a table to be more efficient.

Here is my class design

All I want is to get list of flytoys for a purchase order
- filter on customer name
- get the oldest purchase order

After trying many various methods, here is the code snippet that worked for me:

  public FlyToy[] GetPendingToys(int Count)
        {
            using (var session = OpenSession())
            {
                PurchaseOrder po = null;
                FlyToy toy = null;
                QueryOver subQuery = 
                    QueryOver.Of(() => po)
                        .SelectList(list => list
                            .SelectGroup(() => po.Toy.ID)
                            .SelectMin(() => po.OrderDate))
                        .Where(Restrictions.EqProperty(
                            Projections.Property(() => po.Toy.ID),
                            Projections.Property(() => toy.ID)))
                        .Where(p => p.CustomerName == "Saif");


                var mainQuery = QueryOver.Of(() => toy)
                    .Fetch(t => t.Discount).Eager
                    .WithSubquery.WhereExists(subQuery).Take(Count);

                IList results = mainQuery.GetExecutableQueryOver(session)
                    .List();

                FlyToy[] FlyToys = results.ToArray();
                return FlyToys;
            }
        }

Be nice to nerds. Chances are you'll end up working for one.
- Charles Sykes

Continue Reading

Assigning default value for fields of type string using Fluent NHibernate

Fluent hibernate provides various decent methods that allow you to map class members with table fields. I have tried using the .Default() method while mapping which works fine for numbers but for string datatype this is not working.

So have looked into the actual execution of sql which looks like (ie.. I have added a new member to the class and want all exists rows to have a default value)

update dept
set location = Manchester

Then I have realised single quote(') were missing for the value which should look like

update dept
set location = 'Manchester'

Now I have updated my mapping as follows

Map(x => x.location).Default("'Manchester'").Not.Nullable();

Never trust anything that can think for itself if you can't see where it keeps its brain.
~J.K. Rowling

Continue Reading

Using SQL Server Compact with FluentNHibernate

FluentNHibernate is an alternative to NHibernate that supports Object Relational Mapping framework. Actually FluentNHibernate is build on top of NHibernate that provides strongly typed mapping using c# as opposed to xml mapping (.hbm.xml).

SQL Server CE (Compact edition) is commonly used for small applications as this would not need any packaging and is a simple file copy.

Using FluentNHibernate with SQL Server CE 3.5 file has a flaw in which any changes to mapping would not be reflected in the database unless the database is re-created.

Now this has been solved in the latest version of SQL Server CE 4.0, where in any changes are automatically reflected in the database.

Here is code to configure SQL Server CE

MsSqlCeConfiguration sqlconfig = MsSqlCeConfiguration.Standard.ConnectionString(ConnectionString);
FluentConfiguration fc = Fluently.Configure();
fc = fc.Database(sqlconfig );
ISessionFactory sessionFactory = fc.Cache(c => c
   .UseQueryCache()
   .ProviderClass())
   .Mappings(m => m
   .FluentMappings.AddFromAssemblyOf())
   .ExposeConfiguration((NHibernate.Cfg.Configuration config) => new SchemaUpdate(config)
   .Execute(false, true))
   .BuildSessionFactory();

Telegraphs are machines for conveying information over extensive lines with great rapidity.
Charles Babbage

Continue Reading