Updating a foreign key to Null value using nhibernate

Question

I have two table BuildGroup and table DocumentTemplate. DocumentTemplate table has BuildGroupId as foreign key which is nullable. In a certain senario I update BuildGroupId in DocumentTemplate table.

public bool EditDocTempForBldGrp(int docId, int bldGrpId)
    {
        try
        {
            using (ISession session = Document.OpenSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {
                    HSDocumentTemplate objDocBO = new HSDocumentTemplate();

                    objDocBO = GetDocumentDetailsById(docId);
                    HSBuildGroup objBldGrp = new HSBuildGroup();

                    if (bldGrpId != 0)
                    {                            
                        objBldGrp.Id = bldGrpId;
                    }
                    else
                    {
                        //int ? bldid = null;
                        //objDocBO.HSBuildGroup.Id = null;
                        //objDocBO.HSBuildGroup.Id = DBNull.Value;
                        //objDocBO.HSBuildGroup.Id = -1;

                    }
                    objDocBO.HSBuildGroup = objBldGrp;
                    session.Update(objDocBO);
                    transaction.Commit();
                }
            }
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }

    }

In another senario I need to set BuildGroupId in DocumentTemplate table again to dbnull.value. I tried with different cases as in else block. It giving the error : Cannot implicitly convert type 'System.DBNull' to 'int'. How can I update a foreign key value with NULL? Any help will be appreciated.

kay.herzam · Accepted Answer · 2011-01-31 12:22:05Z

2

A few observations:

You should not assign the Id to a HSBuildGroup object, but instead load the instance via Session.Load() in case the bldGrpId is not 0.
You can just set the build group of a document to null like this:

objDocBO.HSBuildGroup = null;

NHibernate will take care of the rest.

Hope that helps.

answered Jan 31, 2011 at 12:22

kay.herzam

3,0633 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

José F. Romaniello · Accepted Answer · 2011-02-02 11:42:16Z

Use Session.Load and null as Kay Herzam said.

You must to be sure than the document exist to call session.Load. Example:

public bool EditDocTempForBldGrp(int docId, int bldGrpId)
{
    try
    {
        using (ISession session = Document.OpenSession())
        using (ITransaction transaction = session.BeginTransaction())
        {
           session.Get<HSDocumentTemplate>(docId).HSBuildGroup 
                      = bldGrpId = 0 ? null : session.Load<HSBuildGroup>(bldGrpId);
               transaction.Commit();
        }
    }
}

This way nhibernate will execute something like,

select ... from DocumentTemplate where DocId = ..;
UPDATE DocumentTemplate SET .... , BuildGroupId = null where DocumentId = XX;

or

select ... from DocumentTemplate where DocId = ..;
UPDATE DocumentTemplate SET .... , BuildGroupId = YY where DocumentId = XX;

Note there is no select for BuildGroup to the database.

If you are not sure about the existence of the build group, your code should look like:

public bool EditDocTempForBldGrp(int docId, int bldGrpId)
{
        try
        {
            using (ISession session = Document.OpenSession())
            using (ITransaction transaction = session.BeginTransaction())
            {
               session.Get<HSDocumentTemplate>(docId).HSBuildGroup 
                      = session.Get<HSBuildGroup>(bldGrpId);
               transaction.Commit();
            }
         }
}

This way nhibernate will execute something like,

select ... from DocumentTemplate where DocId = ..;
SELECT .... FROM BuildGroup where buildgroupid = ZZ;
UPDATE DocumentTemplate SET .... , BuildGroupId = null where DocumentId = XX;

Get automatically returns null if the object doesn't exist.

Finally you don't need to call Session.Update() this is for reattaching an entity. Everything associated to a Session will be flushed when you commit the transaction.

I think you 've got a tiny error: Shouldn't it be session.Get<HSDocumentTemplate>(docId).HSBuildGroup = bldGrpId == 0 ? null : session.Load<HSBuildGroup>(bldGrpId);

Collectives™ on Stack Overflow

Updating a foreign key to Null value using nhibernate

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related