Mark Monster

Beside column mapping we did in Part 1 of the "Linq to SQL doing it manually" series. We also want to have relationships, this part will be all about relationships. The series contains the following parts:

• Linq to SQL doing it manually: Part 1 - Table and column creation from C#
• Linq to SQL doing it manually: Part 2 - Database creation
• Linq to SQL doing it manually: Part 3 - Relationships

When we take a look at the generated code for a one-to-many relation it has the following looks.

For the one-side:

 1 private EntityRef<Post> _Post;
 2 [Association(Name="Post_PostTagRelation", Storage="_Post", ThisKey="PostId", OtherKey="Id", IsForeignKey=true)]
 3 public Post Post
 4 {
 5   get
 6   {
 7     return this._Post.Entity;
 8   }
 9   set
10   {
11     Post previousValue = this._Post.Entity;
12     if (((previousValue != value)
13           || (this._Post.HasLoadedOrAssignedValue == false)))
14     {
15       this.SendPropertyChanging();
16       if ((previousValue != null))
17       {
18         this._Post.Entity = null;
19         previousValue.PostTagRelations.Remove(this);
20       }
21       this._Post.Entity = value;
22       if ((value != null))
23       {
24         value.PostTagRelations.Add(this);
25         this._PostId = value.Id;
26       }
27       else
28       {
29         this._PostId = default(System.Guid);
30       }
31       this.SendPropertyChanged("Post");
32     }
33   }
34 }
35 
36 private System.Guid _PostId;
37 [Column(Storage="_PostId", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true)]
38 public System.Guid PostId
39 {
40   get
41   {
42     return this._PostId;
43   }
44   set
45   {
46     if ((this._PostId != value))
47     {
48       if (this._Post.HasLoadedOrAssignedValue)
49       {
50         throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
51       }
52       this.OnPostIdChanging(value);
53       this.SendPropertyChanging();
54       this._PostId = value;
55       this.SendPropertyChanged("PostId");
56       this.OnPostIdChanged();
57     }
58   }
59 }

For the many-side:

 1 private EntitySet<PostTagRelation> _PostTagRelations;
 2 [Association(Name="Post_PostTagRelation", Storage="_PostTagRelations", ThisKey="Id", OtherKey="PostId")]
 3 public EntitySet<PostTagRelation> PostTagRelations
 4 {
 5   get
 6   {
 7     return this._PostTagRelations;
 8   }
 9   set
10   {
11     this._PostTagRelations.Assign(value);
12   }
13 }

I think this is a lot of code. So let’s try to minimize this.

For the one-side:

1 [Column(IsPrimaryKey = true)]
2 private Guid PostId { get; set; }
3 
4 [Association(IsForeignKey = true, ThisKey = "PostId")]
5 public Post Post { get; set; }

For the many-side:

1 private IList<PostTagRelation> postTagRelations = new List<PostTagRelation>();
2 [Association(Storage = "postTagRelations", OtherKey = "PostId")]
3 public IList<PostTagRelation> PostTagRelations
4 {
5   get { return postTagRelations; }
6   set { postTagRelations = value; }
7 }

A lot less code I would guess. From 72 lines to just 12 lines. But we are not there yet. After some testing I found out the generated relation is bi-directional, and the relation I created is just uni-directional. What does this mean? This means that if you manipulated either post.PostTagRelations or postTagRelation.Post the other will be changed as well in a bi-directional relation. I remember in the past you had to do something special to make this work with NHibernate. I have no solution for this right now, but you can take a look at how other people did something similar on CodePlex. One of the troubles I find in the solution they propose is the usage of EntityRef and EntitySet. Those are both parts of Linq to SQL and would kind of interfere with my other code-parts, if used.

Another feature that is lost is lazy loading. I must admit I don’t hate it being lost. I think people must think about persistence before doing everything automatically. So if we want to load only the tags we don’t have to anything special. But if we also want to load the PostTagRelations and the Posts with the tags we will have to add some DataLoadOptions. The following code example show how it works.

 1 using (var context = new Context(""))
 2 {
 3   var dataLoadOptions = new DataLoadOptions();
 4   dataLoadOptions.LoadWith<Tag>(t => t.PostTagRelations);
 5   dataLoadOptions.LoadWith<PostTagRelation>(ptr => ptr.Post);
 6   //dataLoadOptions.LoadWith<PostTagRelations>(ptr => ptr.Tag);
 7   context.LoadOptions = dataLoadOptions;
 8 
 9   var dotNetTag = (from tag in context.Tags
10                    where tag.Name.Equals(".NET")
11                    select tag).Single();
12 
13   Assert.Equal(3, dotNetTag.PostTagRelations.Count());
14 
15   var posts = from postTagRelation in dotNetTag.PostTagRelations
16               where postTagRelation.Post != null
17               select postTagRelation.Post;
18   Assert.Equal(3, posts.Count());
19 
20   var tags = from postTagRelation in   dotNetTag.PostTagRelations
21              where postTagRelation.Tag != null
22              select postTagRelation.Tag;
23   Assert.Equal(3, tags.Count());
24 }

The only thing I must admit, line 23 fails. The tags aren’t loaded in the PostTagRelations. I tried adding line 6 but this did throw an exception: "System.InvalidOperationException : Cycles not allowed in LoadOptions LoadWith type graph." I have no solution for this yet, but it has something to do with the bi-directional issue I also found.

There are some areas of improvement on the relationships with Linq to SQL.

This week I started a series on Linq to SQL doing it manually. The serie contains of the following parts:

• Linq to SQL doing it manually: Part 1 - Table and column creation from C#

This part I’ll introduce the actual creation of the database, tables and columns. The people that already have made use of Linq to SQL through the dbml-designer, know the generation of a class that inherits from DataContext. The DataContext class contains an method called CreateDatabase.

But the documentation mentions the algorithm that’s used:

DataContext.CreateDatabase Method

• If a database is identified in the connection string, its name is used.
• If a DatabaseAttribute attribute is present, its Name property is used as the name of the database.
• If there is no database tag in the connection string and a strongly typed DataContext is used, a database that has the same name as the DataContext inheriting class is checked.
• If a weakly typed DataContext is used, an exception is thrown.
• If the DataContext has been created by using a file name, the database corresponding to that file name is created.

I just tried something out to get to know more about it. What I first did, was create a mapping for a few classes so that tables for those classes could be created. I also created a database where I wanted to get generated tables.

So my first approach was the following:

1. Instantiate a DataContext class with a connection-string.
2. Call CreateDatabase method.

1 var context = new DataContext(@"...");
2 context.CreateDatabase();

What happened was an exception. Just according to the algorithm in the documentation. Although the exception message was somewhat strange: "Unable to create database because data context ‘DataContext’ has no tables." I think this is kind of a strange message, but the documentation already mentions this wasn’t going to work.

My second approach.

1. Create a class derived from DataContext.
2. Instantiate the derived class with a connection-string.
3. Call CreateDatabase method on the derived class.

1 public class EmptyContext : DataContext
2 {
3     public EmptyContext(string fileOrServerOrConnection)
4         : base(fileOrServerOrConnection)
5     {
6     }
7 }

var context = new EmptyContext(@"...");
context.CreateDatabase();

Still no luck, I got an exception again. The message was the same though: "Unable to create database because data context ‘DataContext’ has no tables." This got me thinking. The Context needs to know about the mapped classes in some way. So I took a look at the context that’s generated by the designer.

I found get properties for each table/class that was mapped.

My third approach, let’s add some properties.

1. Create a class derived from DataContext.
2. Add properties of Table<mappedClassType> to the DataContext.
3. Call CreateDatabase method on the derived class.

 1 public class Context : DataContext
 2 {
 3     public Context(string fileOrServerOrConnection) : base(fileOrServerOrConnection)
 4     {
 5     }
 6 
 7 
 8     public Table<Tag> Tags
 9     {
10         get { return GetTable<Tag>(); }
11     }
12 
13     public Table<Post> Posts
14     {
15         get { return GetTable<Post>(); }
16     }
17 
18     public Table<PostTagRelation> PostTagRelations
19     {
20         get { return GetTable<PostTagRelation>(); }
21     }
22 }

1 var context = new Context(@"...");
2 context.CreateDatabase();

Very nice, this solution almost created the database. I got an error that the database already existed, so I deleted it, and tried again. Succes!

I was very interesting in doing Linq to SQL manually. Yes manually, not making use of the dbml-designer. And why should I want to do this? I thought it would be interesting to do Linq to SQL without a database being available yet.

I’ve also heard the rumor that Linq to SQL is dead, or almost dead. I’m not sure about this, but I still think Linq to SQL can be appropriate for simple situations, where Linq to Entities is for the more advanced scenario’s it’s also more complex to apply.

Attribute-Based mapping

What I need is Attribute-Based mapping I guess. The documentation talks about 9 different attributes:

• DatabaseAttribute
• TableAttribute
• ColumnAttribute
• AssociationAttribute
• InheritanceMappingAttribute
• FunctionAttribute
• ParameterAttribute
• ResultTypeAttribute
• DataAttribute

In part 1, I will concentrate on the TableAttribute and the ColumnAttribute.

The TableAttribute

The TableAttribute makes sure a class gets mapped to a database table. As we have no table yet, we want to have as less code as possible. The following code makes sure a class gets mapped to a table. The class Tag gets mapped to the table named Tag.

[Table]
public class Tag
{}

Sometimes you want to have some control on the naming of the table. This is possible by just applying the Name property. This is just as simple as the following code. The class Tag gets mapped to the table named Tags.

[Table(Name="Tags")]
public class Tag
{}

This is all I have to say about the TableAttribute.

The ColumnAttribute

The ColumnAttribute is a little more advanced than the TableAttribute. But it can also be as simple as the TableAttribute. This is because all the primitive types are automatically mapped to a database type. But where do we start? Just very simple just add the ColumnAttribute to a property. The property Name gets mapped to the database column named Name.

[Column]
public string Name { get; set; }

If you want the Name property to be mapped to the database column CustomerName we can do the following.

[Column(Name="CustomerName")]
public string Name { get; set; }

It’s possible to have some influence on the type that’s used in the database for a column. I think this can be handy sometimes. For example if you want the Name field to be maximized to 25 characters and also not nullable, you can use this.

[Column(DbType="NVarChar(25) NOT NULL")]
public string Name { get; set; }

But I like the feature that Linq to SQL does the mapping himself. The following diagram is derived from MSDN and shows what mappings are possible.

The default mapping for the CLR types are the following:

We also want some columns to be identifying. This can be done by setting the IsPrimaryKey property that’s part of the ColumnAttribute. The following example makes the property Id the primary key. It’s possible to have multiple properties to define a composed primary key.

[Table]
public class Tag
{
    [Column(IsPrimaryKey = true)]
    public Guid Id { get; set; }

    [Column]
    public string Name { get; set; }
}

Next article will be about the actual generation of the tables and columns from the class definition.

I think the title speaks for itself. I want to create a LINQ query with aggregations on multiple columns where no grouping is applied. This is just something that can be done very simple in plain SQL.

1 SELECT
2   MAX(HorsePower),
3   AVG(Weight)
4 FROM
5   Car

But when you want to create a construction that’s similar in LINQ you will get an compilation error, because of a wrong syntax.

1 var result = from car in sut.Cars
2              select new
3                         {
4                             MaxHorsePower = car.Max(p => p.HorsePower),
5                             AverageWeight = car.Average(p => p.Weight)
6                         };

The trouble is that this doesn’t work. You can do an aggregation on an complete set, like the following. This will result in just two queries.

1 var result2 = (from car in sut.Cars select car.HorsePower).Max();
2 var result3 = (from car in sut.Cars select car.Weight).Average();

You can actually do multiple aggregations at once when using groups. The sad thing is, we don’t always have something to group on. But that can be solved.

 1 var result = (from car in
 2                   (from car in sut.Cars
 3                    select new
 4                               {
 5                                   car.HorsePower,
 6                                   car.Weight,
 7                                   Dummy = string.Empty
 8                               })
 9               group car by new {car.Dummy}
10               into dummyGroup
11                   select new
12                              {
13                                  MaxHorsePower = dummyGroup.Max(p => p.HorsePower),
14                                  AverageWeight = dummyGroup.Average(p => p.Weight)
15                              }).Single();

This solution adds a dummy column called ‘Dummy’ with the same value for every record. Because this value is the same for every record we can safely group on it and expect to have only one group. This solution makes sure we have one query for the database, but with the use of a workaround.

I would say yes this works, but for readability I would not suggest the use of this workaround for the trivial problem above. By using two queries we have a very manageable solution that’s readable from code. The workaround needs at least some comments before we can totally understand it as someone new to the workaround.

But it in the end we can also say, we now know how we can fake the grouping. Use it with care, as I mentioned because of the manageability of your code.

I did show it in my fourth article about Silverlight Networking, using a functionpointer in a call instead of a return type. This was to get the JavascriptWebResponse back asynchronous.

Working with the Silverlight Networking stack is only possible asynchronously. So we need to provide a callback function that get’s called after execution. All the Silverlight examples I’ve seen have this callback function in the User Interface. But what about all the classes that have their own responsibility that normally live in between the User Interface and the actual call to the Networking stack? I’m sure I don’t want to call the Networking stack right from the UI. I normally have at least one class between the UI and the Network call that does some transformation, preparation and other things. Let’s have a solution that I designed for Silverlight but also works within the full CLR.

Read the rest of this entry »

LINQ is one of the new feature we will get in .NET 3.5. I think it’s a very interesting new technology that consist of LINQ the language, and some elements that make use of LINQ for use with XML files or databases. LINQ to SQL is one of the techniques to use LINQ to communicate with the database. There are a few other techniques that use LINQ in combination with the database: LINQ to Entities, LINQ to Dataset.

I started with the 5 part LINQ to SQL series by Scott Guthrie:

1. Using LINQ to SQL
2. Defining our Data Model classes
3. Querying our Database
4. Updating our Database
5. Binding UI using ASP:LinqDataSource control