Archive for the ‘SQL’ Category

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

Screencast on supporting POCO with Linq to SQL

September 29th, 2008 by Sidar Ok

  http://www.developers.ie was kind enough to invite me to do a screencast for them, where I will be talking in .NET Coffee Break show about POCO support in Linq to SQL.

The event is on Thursday, 11:00 A.M Greenwich time. Registration is free through http://www.developers.ie/Webcasts.aspx .

Hope to see you all there !

UPDATE : Thanks to Paschal and http://www.developers.ie, to provide me this opportunity. Here are the source for the demo. Sorry for the unluckiness that connection dropped twice (yes, twice :( ). Thanks everybody for listening, and as son as Pascal provides the link I will post it here.

UPDATE 2: Still waiting the show to go online.

UPDATE 3: Here it is :Part1 , Part2.

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

After Linq to Sql Talk in Cork

September 23rd, 2008 by Sidar Ok

The talk last night was awesome. Thanks to all who showed up, there was a good turnout. Also thanks to Joe Gill and MTUG for organizing the event, and to Microsoft for sponsoring. 

pic1 

I had the great chance of sharing my thoughts, knowledge and experience on Linq to SQL, analyzing upsides and downsides of it. It was targeted for advanced audience, so I enjoyed talking to a bunch of geeks.

Here are the slides for the presentation. I also made a demo on how we can support Domain First Design and create POCOs with Linq to SQL, and didn’t have the time to do the second demo on a short multi tier development demonstration. You can find them here in rar format.

It is always great to have good techies around you, and free beers along with chicken wings !!

This was the beginning event of the year and I am honored to have done it. Hope this will encourage more people to share the views and experiences on subject matters.

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

Agenda on Linq to SQL talk

September 20th, 2008 by Sidar Ok

I decided on some of the bullet points that I will talk about at 22nd. This will be a MS-200 level talk, means that it requires also basic level knowledge of Linq to SQL. Although if you don’t have the basic knowledge, you are still welcome to fill the seats and make the place look crowded :-)

In this first event of the year, here is what I am going to talk about ORM s and Linq to SQL in particular:

  1. Building a Common Glossary
  2. Defining the Problem
  3. Building in house ORM/DAL vs Use an existing one
  4. Linq to SQL Comes into play : Myths and Realities
  5. Linq to SQL beyond drag and drop : Concepts
  6. Linq to SQL Entity Model
  7. Mapping Engine
  8. Attribute Level or External ?

    SQL Metal to rescue

    What it does, what it lacks

  9. Understanding DataContext
  10. Change Management & Change Communication Strategies
  11. Advanced Topics (If time permits)
    1. Debugging and Troubleshooting
    2. Transaction Handling
    3. Concurrency & Conflict Handling Scenarios
    4. Entity Validation
    5. Security Model
    6. Serialization
    7. Performance Advices & Best practices

I also prepared a couple of demos to serve some of the practical implementation of the concepts that I want to share.

Looking forward to see you at Imperial Hotel on Monday!

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

Talking on Linq to SQL on 22nd of September

September 5th, 2008 by Sidar Ok

I am going to be talking about Linq to SQL, one of the ORM mappers coming out of Redmond. I am planning to cover how to utilize Linq to SQL to get the best benefit out of it, Linq to SQL’s place in the ORM world and pros-cons and of course ways to apply patterns and practices with a couple of demos.  I’ll be more than happy to see any of you there and do some geek chat , and have a couple of pints. Pints and chicken tenders are free and Microsoft’s courtesy (Himm, free tenders…That is more appealing then the talk…tenders…)

Registration is free through this link : http://www.cork.mtug.ie/Events/EventInfo.aspx?ID=b2515894-866f-4d70-8c87-ebaa69c69b7d 

 See you there !

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

Localizing Linq to SQL Entities

August 18th, 2008 by Sidar Ok

Back from the holidays! Not getting too much sun certainly encourages to write code rather than chilling out. Writing on this subject was on my list as Linq to SQL got more mature, need for it in multi-cultural applications has arisen respectively. Also an old post of Ayende beat me to think about how a similar problem could be solved in Linq to SQL.

I’ll use the same model that he provided, and it is the following:

tmpD45

Figure 1. Table structure for multi-lingual products

 

As in the original post, the challenge is just to load the Product Names for the current culture(or specific culture), not all of them related to one product. So in nhibernate, there are filters to solve this problem in an elegant way. It is elegant because it is externally configurable and includes no intrusiveness in your design.

When internationalising-localizing comes into play, there are 2 main approaches from a Domain Perspective and it lies behind the answer of the question :

“Is localization a concern of my domain?”

In fairness, the answer changes for every domain (to my experience in most cases it is either no, or part of a different domain, such as administration). A simple way of determining if this is an issue is, to check that if domain needs to know about different cultures or domain elements with different languages need to talk to each other or not (Can Reuters publish news in Portugese ?). If the answer is yes, then even eager loading all language translations can be an option. But otherwise, we’ll need to abstract away so that domain won’t know about this infrastructurel concern.

In the original post, Ayende uses filters in NHibernate. In Linq to SQL we don’t have filters but as mentioned before, we have Load Options to give a criteria and reduce the amount of data we retrieve.

As a matter of fact, we expect following test to pass. Note that this is a state based test to test the data retrieved is not more than one.

   1: /// <summary>
   2: ///A test for GetProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void GetProductTest()
   6: {
   7:   ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:   int prodId = 1; // TODO: Initialize to an appropriate value
   9:   int lcId = 3; // TODO: Initialize to an appropriate value
  10:   Product actual = target.GetProduct(prodId, lcId);
  11:   Assert.AreEqual(“Prod13″, actual.Name);
  12:   Assert.IsTrue(actual.ProductNames.Count == 1);
  13: }

Where the records in the table are as follows:

image

Figure 2. Records in Product Names Table. As seen, there are 2 records for product id ‘1′

The entity structure that we have to use with Linq to SQL (generated by the courtesy of the designer) is as follows:

image

Figure 3. Object Model of Product and Product Name

Looks innocent doesn’t it ? The secret thing is that Product will always have a list of ProductNames, which in my case will always have 1 element. If I want to keep my domain ignorant of this, this certainly is a bad thing but this is what L2S gives me by default. There are ways to overcome this issue of course, but those are not the point of the post.

In addition to the model, I’ll add another field called “Name” to the model that’s not mapped to any column in db, to reach the same example. This is achieved by a partial class:

   1: partial class Product
   2: {
   3:     public string Name
   4:     {
   5:         get;
   6:         set;
   7:     }
   8: }

Now we are ready to write the code that passes the test. Note that we are utilizing AssociateWith Generic Method to make the necessary filtering.

   1: /// <summary>
   2: /// Gets the product for the current culture.
   3: /// </summary>
   4: /// <param name=”prodId”>The prod id.</param>
   5: /// <param name=”lcId”>The lc id to do localization filter.</param>
   6: /// <returns></returns>
   7: public Product GetProduct(int prodId, int? lcId)
   8: {
   9:     using (ProductsDataContext context = new ProductsDataContext())
  10:     {
  11:         // set load options if localizable filter needed
  12:         if (lcId.HasValue)
  13:         {
  14:             DataLoadOptions options = new DataLoadOptions();
  15:             options.AssociateWith<Product>(p => p.ProductNames.Where<ProductName>(pn => pn.CultureId == lcId));
  16:             context.LoadOptions = options;
  17:         }
  18:
  19:         Product pFromDb = context.Products.Single<Product>(p => p.ProductId == prodId);
  20:
  21:         return new Product()
  22:         {
  23:                  Amount = pFromDb.Amount,
  24:                  ProductId = pFromDb.ProductId,
  25:                  Size = pFromDb.Size,
  26:                  Name = pFromDb.ProductNames.First<ProductName>().Name,
  27:                  ProductNames = pFromDb.ProductNames
  28:         };
  29:      }
  30: }

Now since we are done with the original post, let’s go beyond the bar and implement inserts & updates too. With Inserts, there are 2 things that I am going to handle: 1 - It is a brand new insert 2 - It is just an insert of a new product name in another language.

For first one here is the test :

   1: /// <summary>
   2: ///A test for InsertProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void Should_Insert_for_Completely_New_Prod()
   6: {
   7:     ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:     Product p = new Product()
   9:     {
  10:          Amount = 31,
  11:          Name = “English Name”,
  12:              ProductId = 0,
  13:              Size = 36,
  14:     };
  15:     int lcId = 7;
  16:     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
  17:     {
  18:         target.InsertProduct(p, lcId);
  19:         Assert.IsTrue(p.ProductId > 0);
  20:         Assert.IsTrue(p.ProductNames.Count > 0);
  21:      }
  22: }

And for the second one:

   1: /// <summary>
   2: ///A test for InsertProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void Should_Insert_Name_for_Existing_Prod()
   6: {
   7:     ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:     Product p = target.GetProduct(1);
   9:     int firstCount = p.ProductNames.Count;
  10:     p.Name = “Kurdish Name”;
  11:     int lcId = 9;
  12:     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
  13:     {
  14:         target.InsertProduct(p, lcId);
  15:         Product prAfterInsert = target.GetProduct(p.ProductId);
  16:         Assert.AreEqual(firstCount + 1, prAfterInsert.ProductNames.Count);
  17:     }
  18: }

So, passing test is obvious. I need to do an extra insert to the product tables if it is a new one, and that’s it:

   1: /// <summary>
   2: /// Inserts the product.
   3: /// </summary>
   4: /// <param name=”p”>The p.</param>
   5: /// <param name=”lcId”>The lc id.</param>
   6: public void InsertProduct(Product p, int lcId)
   7: {
   8:     using (ProductsDataContext context = new ProductsDataContext())
   9:     {
  10:         if (p.ProductId == 0)
  11:         {
  12:             // insert only if it is new
  13:             context.Products.InsertOnSubmit(p);
  14:         }
  15:
  16:         InsertProductNameForProduct(context, p, lcId);
  17:         context.SubmitChanges();
  18:     }
  19: }
  20:
  21: /// <summary>
  22: /// Inserts the product name for product.
  23: /// </summary>
  24: /// <param name=”context”>The context.</param>
  25: /// <param name=”p”>The p.</param>
  26: /// <param name=”lcId”>The lc id.</param>
  27: private void InsertProductNameForProduct(ProductsDataContext context, Product p, int lcId)
  28: {
  29:     context.ProductNames.InsertOnSubmit(new ProductName()
  30:     {
  31:         CultureId = lcId,
  32:         Name = p.Name,
  33:         ProductId = p.ProductId,
  34:         Product = p,
  35:      });
  36: }

And last, for update; apart from the obvious part there is one situation we need to handle : if the name of the product is changed, than we need to update it as well. For the other fields, go on with the regular update. Here is the test that codifies the statement:

   1: /// <summary>
   2: ///A test for UpdateProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void should_update_product_and_its_current_name()
   6: {
   7:     ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:     Product p = target.GetProduct(1, 2);
   9:     p.Name = “French Name”;
  10:     p.Amount = 40;
  11:     p.Size = 55;
  12:     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
  13:     {
  14:         target.UpdateProduct(p);
  15:         Assert.AreEqual(“French Name”, p.Name);
  16:         Assert.AreEqual(40, p.Amount);
  17:         Assert.AreEqual(55, p.Size);
  18:      }
  19: }

After writing the test, the implementation below becomes obvious:

   1: public void UpdateProduct(Product p)
   2: {
   3:    // since we don’t load more than one product name, we can assume that the one is updated
   4:    using (ProductsDataContext context = new ProductsDataContext())
   5:    {
   6:        context.Products.Attach(p, true);
   7:        ProductName currentName = p.ProductNames.Single<ProductName>();
   8:        if (p.Name != currentName.Name)
   9:        {
  10:            // it is updated, update it
  11:            currentName.Name = p.Name;
  12:         }
  13:         context.SubmitChanges();
  14:     }
  15: }

I showed a possible strategy to localize Linq to SQL entities in this post. Of course, more complex scenarios such as child entities and lazy loading issues could be thought thoroughly, but I hope this gave some initiative to attack the whole idea.

Comments and critics well appreciated as always.

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

Linq to SQL Wish List

June 4th, 2008 by Sidar Ok

As we are not Microsoft marketers, we tend to see the cons of the products that Microsoft builds. As every product has its flaws, of course Linq to SQL is no exception. Here is my list of the things that I compiled from various sites & forums or hit a limitation block by myself:

A) Architecture

1 – Enabled Provider Model & More providers than SQL

Since Matt Warren has explained in his blog post that LINQ to SQL supported multiple providers internally, but it was disabled for some “non-technical” reasons there is an unstoppable desire in each of us to see it enabled in the next version. (I am not even asking for Persistance Ignorance)

2 – Fully Mockable, A Design by Contract Framework

If this happened, we wouldn’t look for hacks like this one. I want to be able to mock DataContext out without doing any funky tweaks.

3 – A Disconnected Data Context

I can not remember how many times I have seen this everywhere. This DataContext will be able to be serialized & deserialized somewhere and even if it is dead, we should be able to benefit from Object Tracking and Deferred Loading.

By this I mean a stateless DAL, where I don’t have to say “delete these children” or “update these but not these”.

4 – Support for more inheritance models

Currently only Table per Hierarchy model is enabled, multiple entities constituting 1 table or vice versa are not.

5 – Out of the box many to many relationships

Title explains, as we currently can’t do this mapping.

6 – Batch Statements Execution

Currently Linq to SQL sends multiple queries to the DB if an operation needs it. A batch statement like NHibernate’s would have been more than cool.

7 – More control on the resulting statement

Advanced users should be able to sneak in the generation or submission process – like the interceptors in NHibernate again.

B) Tools and Designer

1 – Code generation into different structures

The ability to separate each entity and DataContext into different files or assemblies. Partial files do not let us reside our extensions in different assemblies.

2 – Make DBML designer support giving Entity Base Class

SQL Metal has this, so why does the designer not?

3 – Make DBML designer support external mapping

Again, this is a SQL metal specific “magic”.

4 – Enable partial generation in SQL Metal

Sometimes we human beings do not want to generate all the database, just one table for instance.

This is usually followed by a request on being able to “refresh” an object on the design surface. I don’t know anybody who fancies to delete & drop from connection explorer each time something is changed in the db and loose custom associations.

It would also be good if user changes to designer are kept, not overridden everytime by the tool (a smart merge may be?).

5 – A tool to generate POCO translators from/to Linq Entities

This could be configured in code or via XML files. Some of us (including me) are using Software Factories to generate them; it would have been nice to have an out of the box support in Visual Studio.

That’s all I can remember of at the midnight. What would you like to have in this list apart from these?

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

Linq to SQL with WCF in a Multi Tiered Action - Part 2

June 2nd, 2008 by Sidar Ok

In the first part of this article, I tried to define a Users & Favorites scenario and the things to keep in mind about Linq to SQL. In this post I’ll continue building that application and show its implementation in different tiers connected with WCF.

Here are the sources for the article.

Service Layer Design (Cont’d from Part 1)

Service Host (Web Service in our case)

This is a host project (a plain Web project) needed to host our web service. It has our .svc files and needed configuration. In .svc file we have the mapping from contract to the implementation:

<%@ ServiceHost Language=”C#” Debug=”true” 
Service=”ServiceImplementations.UsersService” %>

And the endpoint configuration goes as follows:

   1: <system.serviceModel>
   2:         <behaviors>
   3:             <serviceBehaviors>
   4:                 <behavior name=”FavoritesServiceBehavior”>
   5:                     <serviceMetadata httpGetEnabled=”true” />
   6:                     <serviceDebug includeExceptionDetailInFaults=”false” />
   7:                 </behavior>
   8:                 <behavior name=”UsersServiceBehavior”>
   9:                     <serviceMetadata httpGetEnabled=”true” />
  10:                     <serviceDebug includeExceptionDetailInFaults=”false” />
  11:                 </behavior>
  12:             </serviceBehaviors>
  13:         </behaviors>
  14:         <services>
  15:             <service behaviorConfiguration=”FavoritesServiceBehavior”
  16: name=”ServiceImplementations.FavoritesService”>
  17:                 <endpoint address=”" binding=”wsHttpBinding”
  18: name=”IFavoritesService_Endpoint”
  19:                     contract=”ServiceContracts.IFavoritesService”>
  20:                     <identity>
  21:                         <dns value=”localhost” />
  22:                     </identity>
  23:                 </endpoint>
  24:             </service>
  25:             <service behaviorConfiguration=”UsersServiceBehavior”
  26: name=”ServiceImplementations.UsersService”>
  27:                 <endpoint address=”" binding=”wsHttpBinding”
  28: name=”IUsersService_Endpoint”
  29:                     contract=”ServiceContracts.IUsersService”>
  30:                     <identity>
  31:                         <dns value=”localhost” />
  32:                     </identity>
  33:                 </endpoint>
  34:             </service>
  35:         </services>
  36:     </system.serviceModel>

Service Clients (Consumers)

The client layer is a very thin façade to invoke the requested methods from the channel. Clients are meant to be called through controllers if you are using MVC, and in our case our web application will consume the service so the endpoint configurations will live in web tier:

   1: <system.serviceModel>
   2:     <client>
   3:       <endpoint binding=”wsHttpBinding” bindingConfiguration=”"
   4: contract=”ServiceContracts.IFavoritesService”
   5: address=”http://localhost/WebServiceHost/FavoritesService.svc”
   6:         name=”FavoritesClient”>
   7:         <identity>
   8:           <dns value=”localhost” />
   9:           <certificateReference storeName=”My” storeLocation=”LocalMachine”
  10:             x509FindType=”FindBySubjectDistinguishedName” />
  11:         </identity>
  12:       </endpoint>
  13:       <endpoint binding=”wsHttpBinding” bindingConfiguration=”"
  14: contract=”ServiceContracts.IUsersService”
  15: address=”http://localhost/WebServiceHost/UsersService.svc”
  16:         name=”UsersClient”>
  17:         <identity>
  18:           <dns value=”localhost” />
  19:           <certificateReference storeName=”My” storeLocation=”LocalMachine”
  20:             x509FindType=”FindBySubjectDistinguishedName” />
  21:         </identity>
  22:       </endpoint>
  23:     </client>
  24: </system.serviceModel>

Presentation

The challenge in the presentation tier is we need to maintain the state of the each entity according to the user interaction. For this purpose, I put 2 GridViews , one for Users and One for favorites to enable insert, update, delete and select operations.

We will bind strongly typed collections (IList<User> and IList<Favorite>) to our GridViews and we will use the ID fields of the objects to associate with the gridview, and then use them in the code behind:

Here is the definition for Users GridView:

   1: <asp:GridView ID=”usersGrid” runat=”server”
   2:     AutoGenerateColumns=”False” CellPadding=”4″
   3:     ForeColor=”#333333″ GridLines=”None”
   4:     DataKeyNames=”UserId”
   5:     OnRowDeleting=”usersGrid_RowDeleting”
   6:     OnRowUpdating=”usersGrid_RowUpdating”
   7:     OnSelectedIndexChanged=”usersGrid_SelectedIndexChanged”
   8:     OnSelectedIndexChanging=”usersGrid_SelectedIndexChanging”
   9:     OnRowCancelingEdit=”usersGrid_RowCancelingEdit”
  10:     OnRowEditing=”usersGrid_RowEditing”>
  11:     <RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
  12:     <Columns>
  13:         <asp:CommandField ShowDeleteButton=”True” />
  14:         <asp:TemplateField HeaderText=”First Name”>
  15:             <ItemTemplate>
  16:                 <asp:Label ID=”firstNameLabel” runat=”server”
  17: Text=’<%# Bind(”FirstName”) %>’></asp:Label>
  18:             </ItemTemplate>
  19:             <EditItemTemplate>
  20:                 <asp:TextBox ID=”firstNameTextBox” runat=”server”
  21: Text=’<%# Bind(”FirstName”) %>’></asp:TextBox>
  22:             </EditItemTemplate>
  23:         </asp:TemplateField>
  24:         <asp:TemplateField HeaderText=”Last Name”>
  25:             <ItemTemplate>
  26:                 <asp:Label ID=”lastNameLabel” runat=”server”
  27: Text=’<%# Bind(”LastName”) %>’></asp:Label>
  28:             </ItemTemplate>
  29:             <EditItemTemplate>
  30:                 <asp:TextBox ID=”lastNameTextBox” runat=”server”
  31: Text=’<%# Bind(”LastName”) %>’></asp:TextBox>
  32:             </EditItemTemplate>
  33:         </asp:TemplateField>
  34:         <asp:CommandField ShowEditButton=”True” />
  35:         <asp:CommandField ShowSelectButton=”True” />
  36:     </Columns>
  37:     <FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
  38:     <PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
  39:     <SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
  40:     <HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
  41:     <EditRowStyle BackColor=”#999999″ />
  42:     <AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
  43:     </asp:GridView>

The one for Favorites is pretty much the same so I’ll go over Users grid.

Let’s go to code behind which is more important to us. We are going to do a batch update and send List of Users, and each user in the list will have their favorites. All the entities will have their latest status in their Status field.

Here is a sequence diagram to make things easier and more clearer to understand :

image

Picture 1. Sequence diagram of what’s happening

Now, in the page load, we are going to populate the Users GridView:

   1: if (!IsPostBack)
   2: {
   3:    try
   4:    {
   5:        if (SessionStateUtility.Users == null)
   6:        {
   7:            // error may occur during disposal, not caring for the time being
   8:            using (UsersClient client = new UsersClient())
   9:            {
  10:                SessionStateUtility.Users = client.GetAllUsers().ToList<User>();
  11:            }
  12:        }
  13:        BindUsersGrid(SessionStateUtility.Users, -1);
  14:    }
  15:    catch (Exception ex)
  16:    {
  17:        Response.Write(ex.ToString());
  18:    }
  19: }

In the grid, user can update and delete users from session. For insert, there is a separate panel included at the bottom with an add button. In the add button what we are doing is quite simple, just adding a new user to the session:

   1: protected void addUserButton_Click(object sender, EventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5: 
   6:     User u = new User()
   7:     {
   8:         FirstName = firstNameTextBox.Text,
   9:         LastName = lastNameTextBox.Text,
  10:         EMail = emailTextBox.Text,
  11:         Status = EntityStatus.New,
  12:         UserId = SessionStateUtility.NextUserId,
  13:     };
  14: 
  15:     SessionStateUtility.Users.Add(u);
  16: 
  17:     BindUsersGrid(SessionStateUtility.Users, -1);
  18: }

You’ll notice 2 things here, one of them is the Status is set to Entity Status.New . The other one is the SessionStateUtility class. This acts as a provider and a helper for User lists. The Users list that it provides is the below:

   1: /// <summary>
   2: /// Gets or sets the users.
   3: /// </summary>
   4: /// <value>The users.</value>
   5: public static List<User> Users
   6: {
   7:     get
   8:     {
   9:         Debug.Assert(HttpContext.Current != null);
  10:         Debug.Assert(HttpContext.Current.Session != null);
  11:
  12:         return HttpContext.Current.Session[“Users”] as List<User>;
  13:     }
  14:     set
  15:     {
  16:         Debug.Assert(HttpContext.Current != null);
  17:         Debug.Assert(HttpContext.Current.Session != null);
  18: 
  19:         HttpContext.Current.Session[“Users”] = value;
  20:     }
  21: }

And it provides another method to get NextUserId. This is necessary because since there can be multiple new records in the screen, we will need to identify them. Next User Id brings the next highest negative number that is available:

   1: /// <summary>
   2: /// Gets the next id.
   3: /// </summary>
   4: /// <value>The next id.</value>
   5: public static int NextUserId
   6: {
   7:    get
   8:    {
   9:        if (SessionStateUtility.Users.Count == 0)
  10:        {
  11:            return -1;
  12:        }
  13:        int minId = SessionStateUtility.Users.Min<User>(user => user.UserId);
  14:
  15:        if (minId > 0)
  16:        {
  17:            return -1;
  18:        }
  19: 
  20:        return –minId;
  21:    }
  22: }

And then we need to handle the grid events. I wrote a helper function to Get the User object from Selected row index in the grid (it retrieves from session)”

   1: private User GetUserFromRowIndex(int index)
   2: {
   3:     int userId = usersGrid.DataKeys[index].Value as int? ?? 0;
   4: 
   5:     //retrieve the instance in the session
   6:     User user = SessionStateUtility.Users.Single<User>(usr => usr.UserId == userId);
   7:     return user;
   8: }

Another helper function is there for just to get user’s full name formatted:

   1: private string GetFullNameForUser(User u)
   2: {
   3:     return String.Format(CultureInfo.InvariantCulture, “{0} {1}”, u.FirstName, u.LastName);
   4: }

And this one updates the UI fields for a selected user:

   1: private void UpdateUiForUser(User u)
   2: {
   3:    if (u != null)
   4:    {
   5:        favoritesPanel.Visible = true;
   6:        userNameLabel.Text = GetFullNameForUser(u);
   7:        BindFavoritesGrid(u.Favorites.ToList<Favorite>(), -1);
   8:    }
   9: }

And of course one method for binding the grid:

   1: private void BindUsersGrid(IList<User> users, int editIndex)
   2: {
   3:     usersGrid.DataSource = users
   4:     .Where<User>(usr=>usr.Status != EntityStatus.Deleted);// only bind non deleted ones
   5:     usersGrid.EditIndex = editIndex;
   6:     usersGrid.DataBind();
   7: }

As you can see we are not binding the deleted ones but we are still keeping them in the session because we need to know what is deleted when we send them back to the data tier.

Then within the light of these methods, here goes the SelectedIndex_Changing event handler. It updates the favorite’s grid for the selected user:

   1: protected void usersGrid_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5:     usersGrid.SelectedIndex = e.NewSelectedIndex;
   6: 
   7:     User u = GetUserFromRowIndex(e.NewSelectedIndex);
   8:     UpdateUiForUser(u);
   9: }

And when the row is being edited, following event handler will get executed:

   1: protected void usersGrid_RowEditing(object sender, GridViewEditEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5:
   6:     usersGrid.SelectedIndex = e.NewEditIndex;
   7: 
   8:     BindUsersGrid(SessionStateUtility.Users, e.NewEditIndex);
   9: }

And after user clicks edit, when he/she clicks update following handler will run:

   1: protected void usersGrid_RowUpdating(object sender, GridViewUpdateEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5: 
   6:     int userId = usersGrid.DataKeys[e.RowIndex].Value as int? ?? 0;
   7:     //retrieve the instance in the session
   8:     User user = SessionStateUtility.Users.Single<User>(usr => usr.UserId == userId);
   9:     user.FirstName = (usersGrid.Rows[e.RowIndex].FindControl(“firstNameTextBox”)
  10: as TextBox).Text;
  11:     user.LastName = (usersGrid.Rows[e.RowIndex].FindControl(“lastNameTextBox”)
  12: as TextBox).Text;
  13:
  14:     user.Status = user.Status == EntityStatus.New ?
  15: EntityStatus.New :EntityStatus.Updated; // manage the state
  16: 
  17:     BindUsersGrid(SessionStateUtility.Users, -1);// back to plain mode
  18: }

As you see if the edited users’ current status is already new, then we are not modifying it. But else, the state is changed to the updated.

A similar situation also exists for deletion. Have a look at the handler below:

   1: protected void usersGrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5: 
   6:     User user = GetUserFromRowIndex(e.RowIndex);
   7:     // If user is new and deleted now, we shouldnt send it over the wire again
   8:     if (user.Status == EntityStatus.New)
   9:     {
  10:         SessionStateUtility.Users.Remove(user);
  11:     }
  12:     else
  13:     {
  14:         user.Status = EntityStatus.Deleted;
  15:     }
  16: 
  17:     BindUsersGrid(SessionStateUtility.Users, -1);// back to plain mode
  18: }

We have done our work as a presentation layer, and we are now sending all the data through the service to data layer along with all the information needed for it to manage the generation of the SQL Statements (fingers crossed)

Data Layer Design

Since we are going to implement the IUsersDataAccess contract, we need to implement 4 methods: But I’ll focus on 2 of them especially. First one is GetAllUsers:

   1: /// <summary>
   2: /// Gets all users.
   3: /// </summary>
   4: /// <returns>The list of all users along with their favorites.</returns>
   5: public IList<User> GetAllUsers()
   6: {
   7:     using (FavoritesEntitiesDataContext context = new FavoritesEntitiesDataContext())
   8:     {
   9:         DataLoadOptions options = new DataLoadOptions();
  10:         options.LoadWith<User>(u => u.Favorites);
  11: 
  12:         context.LoadOptions = options; // load with favorites
  13:         context.ObjectTrackingEnabled = false; // retrieving data read only
  14: 
  15:         return context.Users.ToList<User>();
  16:     }
  17: }

As you see, we are telling the context to load every user with their favorites. This can cause some damage if these tables are very big, and there are methods to enhance this experience.

The UpdateUsers(IList) method is a bit more complicated. Here are the list of things that we are going to do:

  • Attach the users to the context who have their status “Updated’ – obvious one

  • Attach the users to the context who have their status “Deleted’ – since the context does not know about an object that is not attached, we need to attach them too.

  • We aren’t going to attach the objects to insert, because Data Context doesn’t need to know about the objects those are being added.

  • Call the relevant of one of those by looking at their status: InsertAllOnSubmit, DeleteAllOnSubmit

  • Do the same for the child entities of each. (Keep in mind that we need to delete all children regardless of their status if their parent is deleted)

So now hopefully the following implementation will be more understandable:

   1: /// <summary>
   2: /// Updates the users list.
   3: /// </summary>
   4: /// <param name=”updateList”>The list of users to perform the operations.</param>
   5: public void UpdateUsers(IList<User> updateList)
   6: {
   7:     using(FavoritesEntitiesDataContext context = new FavoritesEntitiesDataContext())
   8:     {
   9:         context.Users.AttachAll<User>(
  10: updateList.Where<User>(
  11:   usr=>usr.Status == EntityStatus.Updated ||
  12:   usr.Status == EntityStatus.Deleted), true);
  13:         context.Users.InsertAllOnSubmit<User>(
  14: updateList.Where<User>(
  15:   usr=>usr.Status == EntityStatus.New));
  16:         context.Users.DeleteAllOnSubmit<User>
  17: (updateList.Where<User>(usr => usr.Status == EntityStatus.Deleted));
  18: 
  19:         // do the same for the children
  20:         // If the parent is deleted, to prevent orphan records we need to delete
  21:         // children too
  22:         foreach (User user in updateList)
  23:         {
  24:             context.Favorites.AttachAll<Favorite>
  25: (user.Favorites.Where<Favorite>
  26:   (fav=>fav.Status == EntityStatus.Updated
  27:   || fav.Status == EntityStatus.Deleted
  28:   || fav.User.Status == EntityStatus.Deleted
  29:   || fav.User.Status == EntityStatus.Updated));
  30:             //we shouldnt insert the new child records of deleted entities
  31:             context.Favorites.InsertAllOnSubmit<Favorite>
  32: (user.Favorites.Where<Favorite>
  33:   (fav => fav.Status == EntityStatus.New
  34:   && fav.User.Status != EntityStatus.Deleted));
  35:             context.Favorites.DeleteAllOnSubmit<Favorite>
  36: (user.Favorites.Where<Favorite>
  37: (fav => fav.Status == EntityStatus.Deleted ||
  38: fav.User.Status == EntityStatus.Deleted));
  39:         }
  40: 
  41:         context.SubmitChanges();
  42:     }
  43: }

That’s the end of fun(!) folks. As you have seen already, there is some work involved with making Linq to SQL work in Multi Tiered architecture, but it is doable still. Again, download the sources and please don’t hesitate to post any comments, criticisms or crossword puzzles via here or sidarok@sidarok.com, they are all welcome.

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

Linq to SQL with WCF in a Multi Tiered Action – Part 1

May 26th, 2008 by Sidar Ok

In many places, forums, blogs, or techy talks with some colleagues I hear some ongoing urban legends about Linq to SQL I came across:

  • You can not implement multi tiered applications with Linq to SQL

  • Linq to SQL can not be used for enterprise level applications

I can’t say that both of these statements are particularly wrong or right, of course Linq to SQL can not handle every scenario but in fairness it handles most of the scenarios sometimes even better than some other RAD oriented ORM s. In this post I will create a simulation of an enterprise web application, having its Data Access, Services, and Presentation Layers separated and let them communicate with each other (err.., at least from service to UI) through WCF – Windows Communication Foundation.

This will be a couple of (may be more) posts, and this is the first part of it. I’ll post the sample code with the next post.

I have to say that this article is neither an introduction to Linq to SQL nor to WCF, so you need basic knowledge of both worlds in order to benefit from this mash up. We will develop an application step by step with an easy scenario but will have the most important characteristics of a disconnected (from DataContext perspective), multi layered enterprise architecture.

Since this architecture is more scalable and reliable, implementing it with Linq to SQL has also some tricks to keep in mind:

  • Our DataContext will be dead most of the time. So we won’t be able to benefit Object Tracking to generate our SQL statements out of the box.

  • This also brings to the table that we have to know what entities to delete, what to insert, and what to update. We can not just “do it” and submit changes as we are doing it in connected mode. This means that we have to maintain the state of the objects manually (sorry folks, I feel the same pain).

  • The transport of the data over the wire is another problem, since we don’t write the entities on our own(and in the case of an amend to them the designer of Linq to SQL can be very aggressive) so it brings us into 2 common situation

  • We can create our own entities, and write translators to convert from Linq Entities to our very own ones.

  • We can try to customize Linq Entities in the ways we are able to.

Since the first one is obvious and the straight forward to implement, we will go down the second route to explore the boundaries of this customization.

To make it clearer that what I will do, here is a basic but a functional schema of the resulting n-tier application

s

Picture 1 – Architectural schema of the sample app.

In our example, we are going to use Linq to SQL as an ORM Mapper. So as you see in the schema, Linq to SQL doesn’t give us the heaven of not writing a DAL Layer at all. But it reduces both stored queries/procedures and amount of mapping that we had to do manually before.

Developing the Application

Scenario

The scenario I came up with is a favorites web site, that consist of 2 simple pages enabling its users to Insert, Delete, Update and Retrieve users and their favorites when requested. 1 user can have many favorites.

We will simply place 2 Grid Views in the page and handle their events to make the necessary modifications on the model itself. This will also demonstrate a common usage.

Design

Entities

Here is the object diagram of the entities; they are the same as the DB tables:

clip_image004

Picture 2.Entity Diagram

See the additional “Version” fields in the entities; they are type of Binary in .NET and TimeStamps in SQL Server 2005. We will use them to let Linq to SQL handle the concurrency issues for us.

Since we are going to employ a web service by the help of WCF, we need to mark our entities as DataContract to make it available for serialization through DataContractSerializer. We can do that by right clicking on the designer and going to properties, and changing Serialization property to unidirectional as in the picture follows:

clip_image006

Picture 3. Properties window

After doing and saving this we will see in the designer.cs file, we have our Entities marked as DataContract and members as DataMember s.

As mentioned earlier before, we need to maintain our entites state – to know whether they are deleted, inserted, or updated. To do this I am going to define an enumeration as follows:

   1: /// <summary>
   2:     /// The enum helps to identify what is the latest state of the entity.
   3:     /// </summary>
   4:     public enum EntityStatus
   5:     {
   6:         /// <summary>
   7:         /// The entity mode is not set.
   8:         /// </summary>
   9:         None = 0,
  10:         /// <summary>
  11:         /// The entity is brand new.
  12:         /// </summary>
  13:         New = 1,
  14:         /// <summary>
  15:         /// Entity is updated. 
  16:         /// </summary>
  17:         Updated = 2,
  18:         /// <summary>
  19:         /// Entity is deleted. 
  20:         /// </summary>
  21:         Deleted = 3,
  22:     }

We are going to have this field in every entity, so let’s define a Base Entity with this field in it:

   1: [DataContract]
   2: public class BaseEntity
   3: {
   4:   /// <summary>
   5:   /// Gets or sets the status of the entity.
   6:   /// </summary>
   7:   /// <value>The status.</value>
   8: 
   9:   [DataMember]
  10:   public EntityStatus Status { get; set; }
  11: }

 

And then, all we need to do is to create partial classes for our Entities and extend them from base entity:

   1: public partial class User : BaseEntity
   2: {
   3: 
   4: }
   5: 
   6: public partial class Favorite : BaseEntity
   7: {
   8: 
   9: }
  10: 

Now our entities are ready to travel safely along with their arsenal.

Service Layer Design

As we are going to use WCF, we need to have our:

  • Service Contracts (Interfaces)
  • Service Implementations (Concrete classes)
  • Service Clients (Consumers)
  • Service Host (Web service in our case)

Service Contracts

We will have 2 services: Favorites Service and Users Service. It will have 4 methods: 2 Gets and 2 Updates. We will do the insertion, update, and deletion depending on the status so there is no need to determine separate functions for all. Here is the contract for User:

   1: /// <summary>
   2: /// Contract for user operations 
   3: /// </summary>
   4: 
   5: [ServiceContract]
   6: public interface IUsersService
   7: {
   8: /// <summary>
   9: /// Gets all users.
  10: /// </summary>
  11: /// <returns></returns>
  12: 
  13:   [OperationContract]
  14:   IList<User> GetAllUsers();
  15: 
  16: /// <summary>
  17: /// Updates the user.
  18: /// </summary>
  19: /// <param name=”user”>The user.</param>
  20: 
  21:   [OperationContract]
  22:   void UpdateUser(User user);
  23: 
  24: /// <summary>
  25: /// Gets the user by id.
  26: /// </summary>
  27: /// <param name=”id”>The id.</param>
  28: /// <returns></returns>
  29: 
  30:   [OperationContract]
  31:   User GetUserById(int id);
  32: 
  33: /// <summary>
  34: /// Updates the users in the list according to their state.
  35: /// </summary>
  36: /// <param name=”updateList”>The update list.</param>
  37: 
  38:   [OperationContract]
  39:   void UpdateUsers(IList<User> updateList);
  40: }

And here is the contract for Favorites Service:

   1: /// <summary>
   2: /// Contract for favorites service
   3: /// </summary>
   4: [ServiceContract]
   5: public interface IFavoritesService
   6: {
   7:   /// <summary>
   8:   /// Gets the favorites for user.
   9:   /// </summary>
  10:   /// <param name=”user”>The user.</param>
  11:   /// <returns></returns>
  12:   [OperationContract]
  13:   IList<Favorite> GetFavoritesForUser(User user);
  14: 
  15:   /// <summary>
  16:   /// Updates the favorites for user.
  17:   /// </summary>
  18:   /// <param name=”user”>The user.</param>
  19:   [OperationContract]
  20:   void UpdateFavoritesForUser(User user);
  21: }

Service Implementations (Concrete classes)

Since we are developing a db application with no business logic at all, the service layer implementors are pretty lean & mean. Here is the Service implementation for UserService

   1: [ServiceBehavior(IncludeExceptionDetailInFaults=true)]
   2: public class UsersService : IUsersService
   3: {
   4:     IUsersDataAccess DataAccess { get; set; }
   5: 
   6:     public UsersService()
   7:     {
   8:         DataAccess = new UsersDataAccess();
   9:
  10:     }
  11: 
  12:     #region IUsersService Members
  13: 
  14:     /// <summary>
  15:     /// Gets all users.
  16:     /// </summary>
  17:     /// <returns></returns>
  18:     [OperationBehavior]
  19:     public IList<User> GetAllUsers()
  20:     {
  21:         return DataAccess.GetAllUsers();
  22:     }
  23: 
  24:     /// <summary>
  25:     /// Updates the user.
  26:     /// </summary>
  27:     /// <param name=”user”>The user.</param>
  28:     [OperationBehavior]
  29:     public void UpdateUser(User user)
  30:     {
  31:         DataAccess.UpdateUser(user);
  32:     }
  33: 
  34:     /// <summary>
  35:     /// Gets the user by id.
  36:     /// </summary>
  37:     /// <param name=”id”>The id.</param>
  38:     /// <returns></returns>
  39:     [OperationBehavior]
  40:     public User GetUserById(int id)
  41:     {
  42:         return DataAccess.GetUserById(id);
  43:     }
  44: 
  45:     /// <summary>
  46:     /// Updates the users in the list according to their state.
  47:     /// </summary>
  48:     /// <param name=”updateList”>The update list.</param>
  49:     [OperationBehavior]
  50:     public void UpdateUsers(IList<User> updateList)
  51:     {
  52:         DataAccess.UpdateUsers(updateList);
  53:     }
  54: 
  55:     #endregion
  56: }

And as you can imagine the favorite service implementation is pretty much the same.

This has been long enough, so let’s cut it here. In the next post, I will talk about the presentation, service and data layer implementations. By that, we will see how to best approach to modifying these entities in a data grid, pass them through the WCF Proxy and commit the changes (insert, update, delete) to the SQL 2005 database. I will also provide the source codes with the next post. Stay tuned until then.

For part 2 : http://www.sidarok.com/web/blog/content/2008/06/02/linq-to-sql-with-wcf-in-a-multi-tiered-action-part-2.html .

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