Archive for October, 2008

Lazy Loading with Linq to SQL POCO s

October 29th, 2008 by Sidar Ok

Yes, here is that post. While doing Linq to SQL POCO screen cast, and writing the last post about how to achieve POCOs, one of the issues that came up was how to do lazy loading - since we are using pure IList, we of course were not getting lazy loading. That’s something that we didn’t want, so there had to be some workaround this limitation.

The reason that it took me a while to come up with a fair solution, is not that only I am very lazy, but also this was a challenging task. Before I outline the solution, let me explain my train of thoughts so you would understand my pain while hitting the Linq to SQL design decision blocks.

PITA Points

So, to get lazy loading, what we need to do is to intercept a call to a collection, then load it because it is requested at that time. For this, an obvious solution is using Dynamic Proxies. For this matter, I chose LinFu Dynamic proxy and it gave me lots of playground. This is in one pocket.

The most usual thing to do would be, to make the process transparent from user as much as possible. So my idea was to Proxy out the table definitions in context, and I went ahead for that, but what’s that? Table<T> is sealed ! Well done ! So I can’t proxy the sealed tables, because DynamicProxy works on an inheritance basis (with LinFu duck typing, it is possible to unseal, but again Table<T> doesn’t have 1 aggregate interface to choose as a contract. Nice isn’t it ?).

Then I have to proxy the entities, and their related properties. This comes with the implication of marking the to-be lazily loaded properties as virtual.

And another point is that EntitySet doesn’t have a non-generic implementation, so I can’t reach the association without type. This comes with another implication that the Interceptor I am going to write needs to know about the relationship to load, but hey, we can hide this into a repository, and that’s what repositories are for, aren’t they ? (This will make more sense at the end of the post)

Let’s go ahead !

I am still going to use the same simple Questions - Answers model from the last article, but I need to slightly change Question entity as following (it is still a POCO):

   1: public class Question
   2: {
   3:     private int _QuestionId;
   4:
   5:     public virtual int QuestionId
   6:     {
   7:         get
   8:         {
   9:             return _QuestionId;
  10:         }
  11:         set
  12:         {
  13:             _QuestionId = value;
  14:         }
  15:     }
  16:
  17:     private string _QuestionText;
  18:
  19:     public virtual string QuestionText
  20:     {
  21:         get
  22:         {
  23:             return _QuestionText;
  24:         }
  25:         set
  26:         {
  27:             _QuestionText = value;
  28:         }
  29:     }
  30:
  31:     private IList<Answer> _Answer;
  32:
  33:     public virtual IList<Answer> Answer
  34:     {
  35:         get
  36:         {
  37:             return _Answer;
  38:         }
  39:         set
  40:         {
  41:             _Answer = value;
  42:         }
  43:     }
  44: }

Answers list is virtual from now on,so that LinFu can override comfotably.

So let’s look at the tests for lazy loading. What I am going to check for it first is, of course, after getting the instance can I demand for the list and get it successfully ? For e.g, can I do a count ?

   1: [TestMethod()]
   2: public void should_get_correct_answer_count_when_lazily_loaded()
   3: {
   4:   LazyLoadingRepository target = new LazyLoadingRepository(); // TODO: Initialize to an appropriate value
   5:   int id = 1; // TODO: Initialize to an appropriate value
   6:   using (QuestionDataContext context = new QuestionDataContext())
   7:   {
   8:     Question actual;
   9:     actual = target.GetQuestion(context, id);
  10:     Assert.IsNotNull(actual);
  11:     Assert.AreEqual(actual.Answer.Count, 1);
  12:    }
  13: }

Note that this can always be further refactored in order to get by specification, which I am leaving that as an exercise to reader (I always wanted to do that).

Now I need to do a negative test, to see that function does not fool me and does not eagerly load everything. So when I try to access the answers without a context, I should get a Data Context disposed exception:

   1: [TestMethod()]
   2: public void should_throw_when_lazily_loaded_and_reached_outside_the_context()
   3: {
   4:   LazyLoadingRepository target = new LazyLoadingRepository(); // TODO: Initialize to an appropriate value
   5:   int id = 1; // TODO: Initialize to an appropriate value
   6:   Question actual;
   7:   using (QuestionDataContext context = new QuestionDataContext())
   8:   {
   9:      actual = target.GetQuestion(context, id);
  10:      Assert.IsNotNull(actual);
  11:   }
  12:
  13:   try
  14:   {
  15:     int count = actual.Answer.Count;
  16:   }
  17:   catch (Exception ex)
  18:   {
  19:      Assert.IsInstanceOfType(ex, typeof(ObjectDisposedException));
  20:      return;
  21:    }
  22:    throw new Exception(“Should have thrown Object disposed exception, sorry !”);
  23: }

Good, my tests are failing, what a depressive world we inhabit. With the previous implementation which was without lazy loading, The first test would fail with “Object Reference not set to instance of an object” exception, and int the second test Assert would fail because the exception is a NullReferenceException, not an ObjectDisposedException.

Now let’s try to pass these tests with some magic. Here is where LinFu calls us to the dark side. In GetQuestion, we are not going to return the actual object, but instead a proxied Question object. To create a proxy with LinFu, we need a custom interceptor which implements Linfu.DynamicProxy.IInvokeWrapper. Our invoke wrapper needs to know the DataContext, to load the entities and the relationship specification to load the related data. With the light of this info, here is how GetQuestion looks like :

   1: public Question GetQuestion(QuestionDataContext context, int id)
   2: {
   3:   EntityInvokeWrapper<Answer> interceptor = new EntityInvokeWrapper<Answer>(context, (Answer a) => a.QuestionId == id);
   4:   ProxyFactory factory = new ProxyFactory();
   5:   Question retVal = factory.CreateProxy<Question>(interceptor);
   6:   return retVal;
   7: }

This will help me to override Answers list, and replace it with my own implementation. In IInvokeWrapper, we need to implement BeforeInvoke, DoInvoke and AfterInvoke. We are only interested in DoInvoke. I am trying to not to reinvent the wheel, so in background I am using EntitySet’s lazy loading mechanism but that’s transparent to user, since I am proxying the IList<T> with an EntitySet<T> too. But how do I assoicate it with the table in the context and the relationship that I get ? Here is the answer:

   1: public class EntityInvokeWrapper<TChi> : IInvokeWrapper
   2:        where TChi : class
   3: {
   4:   private DataContext Context
   5:   {
   6:     get;
   7:     set;
   8:   }
   9:
  10:   private Func<TChi, bool> RelationshipSpecification
  11:   {
  12:     get;
  13:     set;
  14:   }
  15:
  16:   public EntityInvokeWrapper(DataContext context, Func<TChi, bool> relationship)
  17:   {
  18:     this.Context = context;
  19:     this.RelationshipSpecification = relationship;
  20:   }
  21:
  22:   #region IInvokeWrapper Members
  23:
  24:   public void AfterInvoke(InvocationInfo info, object returnValue)
  25:   {
  26:      //Console.WriteLine(”After”);
  27:   }
  28:
  29:   public void BeforeInvoke(InvocationInfo info)
  30:   {
  31:     //Console.WriteLine(”Before”);
  32:   }
  33:
  34:   public object DoInvoke(InvocationInfo info)
  35:   {
  36:     //Console.WriteLine(”During”);
  37:     string name = info.TargetMethod.Name;
  38:     if (name.StartsWith(“get_”) &&
  39:   info.TargetMethod.ReturnType.GetInterfaces().Contains(typeof(IEnumerable<TChi>)))
  40:     {
  41:       //Console.WriteLine(”Enumerable detected!”);
  42:       EntitySet<TChi> wrapper = new EntitySet<TChi>();
  43:       wrapper.SetSource(this.Context.GetTable<TChi>().Where<TChi>(this.RelationshipSpecification));
  44:       return wrapper;
  45:     }
  46:
  47:     return OriginalCall(info);
  48:    }
  49:
  50:   private object OriginalCall(InvocationInfo info)
  51:   {
  52:     //Console.WriteLine(”Original = ” + info.Target);
  53:     return info.TargetMethod.Invoke(info.Target, info.Arguments);
  54:    }
  55:
  56:     #endregion
  57: }

So as you see, we are doing the magic in DoInvoke, and checking that if it is a property, with an access to the enumeration of the child type (TChi) that we are interested in, we are silently stepping and saying that, hey, what you need for this is an EntitySet, but you can use it as an IList ;) Line 43 does the association between our EntitySet and the context table, and we are returning the EntitySet which has the full lazy loading support - so when it is accessed, it will perform the necessary query on its source. But the Actual Entity is clueless about what’s happening, and all the consumers of the entity who need that lazy loading will treat it as List<T> as it is one.

Conclusion

In this article, I outlined a solution to enable the lazy loading while using POCOs in Linq to SQL. I tried to reuse as much as possible, and wanted to show the pain points what kept me away from a best design. Of course I expect this will always not be a comprehensive solution, but as always, if it gives some ideas, I am happy.

Before leaving, be sure to check out great LinFu stuff from Philip Laureno as it also has other goodies such as simulated duck typing and mixin support, and even a dependency injection framework. Comments and free beers are welcome as usual.

kick it on DotNetKicks.com

Share it on: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Blogosphere News
  • e-mail
  • YahooMyWeb
  • DotNetKicks
  • DZone

Achieving POCO s in Linq to SQL

October 14th, 2008 by Sidar Ok

After the nice talk with developers.ie, it is really nice to see that people have interest in the topic. Unfortunately the quality of the recording was not very nice and connection dropped twice, so I decided to put together this blog post to show how we can work with leaving persistence polluted entities on our behind.

Why is it so important ?

I can hear lots of comments from people around me mainly concerning around “Why do we need this much hassle, when we can already have designer support, and VS integrated goodies of an ORM mapper ?”. First, I have to say that it is fair enough to think in this way. But when things start to go beyond trivial, you start to have problems with persistence or technology polluted entities. On top of my head, I can think of the following:

  1. Technology Agnosticism is a bliss : This concept is usually revolving around PI (Persistence Ignorance), but it is not only that. Persistence Ignorance means that your entities should be cleared of any persistence related code constraints that a framework - usually an ORM - forces on you. This is, for e.g. if you have attribute level mapping where those attributes are not part of your domain but are there just because some framework wants them to be there, then your domain is not persistence ignorant. Or, if your framework requests you to have specific types for handling associations to be used, like EntitySet and EntityRef s in Linq to SQL , same goes for you. This can also be another technology that wants your entities to be serializable for some reason. We need to try to avoid them as much as possible and concentrate on our business concerns there, not to bend our domain to be fitting into those technological discrepancies. This approach will also promote testability. The same goes for the need of implementing an abstract class, or interfaces like INotifyPropertyChanged when you don’t want them.

  2. Relying on Linq to SQL Designer is painful: Designer puts everything in one file, regenerates files each time when you save so you loose your changes such as xml comments. Needless to say, the only OOTB support is attribute level configuration, even for XML you need to use sqlmetal tool out of designer process.

  3. Configuration should not be anything that your domain to be concerned about: Unless you are building a configuration system :)

Let’s get geared

In the light of this, when we are working with Linq to SQL designer, we tend to think that it is impossible to achieve POCOs, but indeed it is: solution is don’t ditch POCOs, just ditch the designer :) While implementing POCOs, we need to know a couple of things beforehand about Linq to SQL internals, because we will be on our own when we have any problems.

  1. EntitySet and EntityRef are indeed useful classes, and they are there to achieve something. When you add an entity to an association, EntitySet manages the identity and back references. That is, for children you need to assign the correct parent id to the child otherwise you will loose relationship. Same goes for EntityRef and for 1-1 relations.

  2. INotifyPropertyChanging and INotifyPropertyChanged are there not only because of informing us by providing the ability to subscribe to necessary events and get notified when a property is changed, but to leverage lazy loading as well. When we discard them, we are back to eager loading.

Enough Rambling, let me see the wild world of code

For this post, I will only focus on the first part, so the lazy loading is a matter of another one. The approach we are going to take is, use the XML mapping instead of attribute based modeling. I am gonna use the trivial Questions and Answers model, where one question can have multiple Answers associated to them. Here is how it looks like :

 

image

Question and Answers entities

And their related code is pretty simple, nothing fancy. Here is the Answer POCO :

 

   1: public class Answer
   2: {
   3:
   4:     public Answer()
   5:     {
   6:     }
   7:
   8:     private int _QuestionId;
   9:
  10:     public int QuestionId
  11:     {
  12:         get
  13:         {
  14:             return _QuestionId;
  15:         }
  16:         set
  17:         {
  18:             _QuestionId = value;
  19:         }
  20:     }
  21:
  22:
  23:     private int _AnswerId;
  24:
  25:     public int AnswerId
  26:     {
  27:         get
  28:         {
  29:             return _AnswerId;
  30:         }
  31:         set
  32:         {
  33:             _AnswerId = value;
  34:         }
  35:     }
  36:
  37:     private string _AnswerText;
  38:
  39:     public string AnswerText
  40:     {
  41:         get
  42:         {
  43:             return _AnswerText;
  44:         }
  45:         set
  46:         {
  47:             _AnswerText = value;
  48:         }
  49:     }
  50:
  51:     private bool _IsMarkedAsCorrect;
  52:
  53:     public bool IsMarkedAsCorrect
  54:     {
  55:         get
  56:         {
  57:             return _IsMarkedAsCorrect;
  58:         }
  59:         set
  60:         {
  61:             _IsMarkedAsCorrect = value;
  62:         }
  63:     }
  64:
  65:
  66:     private int _Vote;
  67:
  68:     public int Vote
  69:     {
  70:         get
  71:         {
  72:             return this._Vote;
  73:         }
  74:         set
  75:         {
  76:             _Vote = value;
  77:         }
  78:     }
  79: }

Yeah, clean, pure C#: No attributes, EntityRef s, nothing. Same goes for Questions as well, where the association is achieved through the good old simple List<T>:

 

   1: public class Question
   2: {
   3:     private int _QuestionId;
   4:
   5:     public int QuestionId
   6:     {
   7:         get
   8:         {
   9:             return _QuestionId;
  10:         }
  11:         set
  12:         {
  13:             _QuestionId = value;
  14:         }
  15:     }
  16:
  17:     private string _QuestionText;
  18:
  19:     public string QuestionText
  20:     {
  21:         get
  22:         {
  23:             return _QuestionText;
  24:         }
  25:         set
  26:         {
  27:             _QuestionText = value;
  28:         }
  29:     }
  30:
  31:     private List<Answer> _Answer;
  32:
  33:     public List<Answer> Answer
  34:     {
  35:         get
  36:         {
  37:             return _Answer;
  38:         }
  39:         set
  40:         {
  41:             _Answer = value;
  42:
  43:         }
  44:     }
  45: }

To use these entities as POCOs, I need a way to externally define the mappings between db tables, columns to the relevant object fields. I chose the other OOTB supported way, XML. As I am so lazy to write it on my own, I ran the following sql metal command to generate it from the DB:

 

   1: sqlmetal /server:sidarok-pc /database:QuestionsAnswers /code:a.cs /map:Questions.xml

As you see, it also generates the code in a.cs file but I am gonna throw it out. Let’s check if the generated XML maps to our fields:

 

   1: <?xml version=”1.0″ encoding=”utf-8″?>
   2: <Database Name=”questionsanswers” xmlns=”http://schemas.microsoft.com/linqtosql/mapping/2007″>
   3:   <Table Name=”dbo.Answer” Member=”Answer”>
   4:     <Type Name=”Answer”>
   5:       <Column Name=”AnswerId” Member=”AnswerId” Storage=”_AnswerId” DbType=”Int NOT NULL IDENTITY” IsPrimaryKey=”true” IsDbGenerated=”true” AutoSync=”OnInsert” />
   6:       <Column Name=”QuestionId” Member=”QuestionId” Storage=”_QuestionId” DbType=”Int NOT NULL” />
   7:       <Column Name=”AnswerText” Member=”AnswerText” Storage=”_AnswerText” DbType=”Text NOT NULL” CanBeNull=”false” UpdateCheck=”Never” />
   8:       <Column Name=”IsMarkedAsCorrect” Member=”IsMarkedAsCorrect” Storage=”_IsMarkedAsCorrect” DbType=”Bit NOT NULL” />
   9:       <Column Name=”Vote” Member=”Vote” Storage=”_Vote” DbType=”Int NOT NULL” />
  10:       <Association Name=”FK_GoodAnswer_Question” Member=”Question” Storage=”_Question” ThisKey=”QuestionId” OtherKey=”QuestionId” IsForeignKey=”true” />
  11:     </Type>
  12:   </Table>
  13:   <Table Name=”dbo.Question” Member=”Question”>
  14:     <Type Name=”Question”>
  15:       <Column Name=”QuestionId” Member=”QuestionId” Storage=”_QuestionId” DbType=”Int NOT NULL IDENTITY” IsPrimaryKey=”true” IsDbGenerated=”true” AutoSync=”OnInsert” />
  16:       <Column Name=”QuestionText” Member=”QuestionText” Storage=”_QuestionText” DbType=”NVarChar(300) NOT NULL” CanBeNull=”false” />
  17:       <Association Name=”FK_GoodAnswer_Question” Member=”Answer” Storage=”_Answer” ThisKey=”QuestionId” OtherKey=”QuestionId” DeleteRule=”NO ACTION” />
  18:     </Type>
  19:   </Table>
  20: </Database>

Now, let’s write the simple select test to see if it just works. This repository test is intentionally an integration test, to see that if I can get the question entity along with its children:

 

   1: [TestMethod()]
   2: public void GetQuestionTest()
   3: {
   4:   QuestionsRepository target = new QuestionsRepository(); // TODO: Initialize to an appropriate value
   5:   int id = 2; // TODO: Initialize to an appropriate value
   6:   Question actual;
   7:   actual = target.GetQuestion(id);
   8:   Assert.IsNotNull(actual);
   9:   Assert.IsTrue(actual.Answer.Count > 0);
  10: }

And after this the implementation is quite trivial. Just note the eager loading that is needed explicitly because otherwise the Answers list will never get assigned and remain null :

 

   1: public Question GetQuestion(int id)
   2: {
   3:     using (QuestionDataContext context = new QuestionDataContext())
   4:     {
   5:         DataLoadOptions options = new DataLoadOptions();
   6:         options.LoadWith<Question>(q => q.Answer);
   7:
   8:         context.LoadOptions = options;
   9:         return context.Questions.Single<Question>(q => q.QuestionId == id);
  10:     }
  11: }

Aha,we don’t have a DataContext yet ! Let’s create it, we need to feed with connection string and XML file. Note the Table<T> implementations are there just for convenience:

 

   1: public class QuestionDataContext : DataContext
   2: {
   3:   static XmlMappingSource source = XmlMappingSource.FromXml(File.ReadAllText(@”C:UserssidarokDesktopPocoDemoPocoDemoquestions.xml”));
   4:   static string connStr = “Data Source=sidarok-pc;Initial Catalog=QuestionsAnswers;Integrated Security=True”;
   5:   public QuestionDataContext()
   6:     : base(connStr, source)
   7:   {
   8:   }
   9:
  10:   public Table<Question> Questions
  11:   {
  12:     get
  13:     {
  14:       return base.GetTable<Question>();
  15:     }
  16:    }
  17:
  18:    public Table<Answer> Answers
  19:    {
  20:      get
  21:      {
  22:        return base.GetTable<Answer>();
  23:      }
  24:     }
  25: }

Now the test passes, hurray, we are happy let’s party! Before let’s take a step forward and write a test for Insert:

 

   1: [TestMethod()]
   2: public void InsertQuestionTest()
   3: {
   4:   QuestionsRepository target = new QuestionsRepository(); // TODO: Initialize to an appropriate value        
   5:   Question question = new Question()
   6:   {
   7:     QuestionText = “Temp Question”,
   8:     Answer = new List<Answer>()
   9:     {
  10:       new Answer()
  11:       {
  12:         AnswerText = “Temp Answer 1″,
  13:         IsMarkedAsCorrect = true,
  14:         Vote = 10,
  15:        },
  16:        new Answer()
  17:        {
  18:          AnswerText = “Temp Answer 2″,
  19:          IsMarkedAsCorrect = false,
  20:          Vote = 10,
  21:         },
  22:         new Answer()
  23:         {
  24:           AnswerText = “Temp Answer 3″,
  25:           IsMarkedAsCorrect = true,
  26:           Vote = 10,
  27:          },
  28:       }
  29:       };
  30:
  31:       using (TransactionScope scope = new TransactionScope())
  32:       {
  33:         target.InsertQuestion(question);
  34:         Assert.IsTrue(question.QuestionId > 0);
  35:         Assert.IsTrue(question.Answer[0].AnswerId > 0);
  36:         Assert.IsTrue(question.Answer[1].AnswerId > 0);
  37:         Assert.IsTrue(question.Answer[2].AnswerId > 0);
  38:        }
  39: }

Simple insert test, insert questions along with its children, answers and check that if they have been assigned any Ids. The implementation is again, nothing different from the usual implementation :

 

   1: public void InsertQuestion(Question q)
   2: {
   3:     using (QuestionDataContext context = new QuestionDataContext(connStr))
   4:     {
   5:         context.Questions.InsertOnSubmit(q);
   6:         context.SubmitChanges();
   7:     }
   8: }

When we run this test, we will run into this error:

 

   1: Test method QuestionRepositoryTest.QuestionsRepositoryTest.InsertQuestionTest threw exception:  System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_GoodAnswer_Question”. The conflict occurred in database “QuestionsAnswers”, table “dbo.Question”, column ‘QuestionId’.
   2: The statement has been terminated

Aha, well this was kinda expected. We knew that we had to maintain the identity and back references, but we didn’t. Shame on us. But how are we gonna do that ? We don’t know the ID value before we insert, how do we tell Linq to SQL to pick the new identity ? Are we back to square 1, @@IDENTITY_SCOPE ?

Of course if I am writing this post, the answer has to be no :) The secret is in the back reference, the back reference is there just because for this matter.

What we need to do now is, in each Answer we need to preserve a reference to the parent Question and for each question that is added, or when the list is overriden we need to assign the Answer’s QuestionId property to this back reference’s one. As we now don’t have the EntitySet, we need to do that on our own, but it is easy enough. For Answers, here is the back reference:

 

   1: private Question _Question;
   2:
   3: public Question Question
   4: {
   5:     get
   6:     {
   7:         return this._Question;
   8:     }
   9:     set
  10:     {
  11:         this._Question = value;
  12:         this._QuestionId = value.QuestionId;
  13:     }
  14: }

And for Question POCO, when the List is overriden, we need to put our own logic to handle this, which is: for every child answer, ensure that back reference and the reference id is set:

 

   1: private List<Answer> _Answer;
   2:
   3: public List<Answer> Answer
   4: {
   5:    get
   6:    {
   7:        return _Answer;
   8:    }
   9:    set
  10:    {
  11:        _Answer = value;
  12:        foreach (var answer in _Answer)
  13:        {
  14:            answer.QuestionId = this.QuestionId;
  15:            answer.Question = this;
  16:        }
  17:    }
  18: }

And Test passes after doing this.  Hope this gives some idea what you can do and what you need to know beforehand.

Conclusion

Desire to decouple domain entities from Technological aspects is important in SoC & SRP, and these principles are important for nearly everything, varying from pure basic to DDD. To achieve this in Linq to SQL, we need to say good bye rid to EntiyRef, EntitySet, INotifyPropertyChanged, INotifyPropertyChanging interfaces.

The next subject I am going to attack is Lazy Lading with POCOs, stay tuned till then !

kick it on DotNetKicks.com

Share it on: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Blogosphere News
  • e-mail
  • YahooMyWeb
  • DotNetKicks
  • DZone