Last time, we tweaked our code to make it a little easier to understand and maintain. So that means we get to dirty it up again!  Our current object model has very simple mapping of one table to one class, with no associations between them. In the real world, this doesn't happen too often (and if it does, you are probably doing something wrong). A relational database has relations. Crazy, yes, but it means our model must be able to handle that.

Let's say then, that the Person object can possess all of the TpsReport objects they have created. To represent that, we'll need to update three things, our table structure, the classes, and the mapping file.

The table structure involves creating a foreign key between the two tables:

USE [LinqTest]
GO
ALTER TABLE [dbo].[TPS_Report] WITH CHECK ADD CONSTRAINT [FK_TPS_Report_Person] FOREIGN KEY([reporter_id])
REFERENCES [dbo].[Person] ([id])
GO
ALTER TABLE [dbo].[TPS_Report] CHECK CONSTRAINT [FK_TPS_Report_Person]

The Person & TpsReport classes use standard objects to relate to each other:

public partial class Person {
  /* snip */
  private List<TpsReport> _reports; // can't use auto properties. grumble.

  public List<TpsReport> Reports { // at least we can use private setters
    get { return this._reports; }
    private set { this._reports = value; }
  }

  public Person() {
    this.Reports = new List<TpsReport>();
  }

  /* snip */
}

public partial class TpsReport {
  /* snip */

  private Person _reporter;

  public Person Reporter {
    get { return this._reporter; } 
    set { this._reporter = value; }
  }

  /* snip */
}

As for the mapping file, use sqlmetal.exe to regenerate it, but don't overwrite your existing file, or you'll have to redo all the changes to the column fields from part 1. In this new file, did you notice the extra <Association> tags in the types? These map the foreign keys to our classes. Copy and paste them to your embedded map file. Once again, you'll need to edit them to match our class structure.

• Name - The name of the foreign key, shouldn't need to be changed.
• Member - The property name.
• Storage - The private member.
• ThisKey - That type's Member of the foreign key. (Id on Person, ReporterId on TpsReport)
• OtherKey - The other type's Member of the foreign key.

This is what you should end up with:

<Database Name="LinqTest" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="dbo.Person" Member="Person">
    <Type Name="LinqExample.Core.Person">
      <!-- snip -->
      <Association Name="FK_TPS_Report_Person" Member="Reports" Storage="_reports" ThisKey="Id" OtherKey="ReporterId" DeleteRule="NO ACTION" />
    </Type>
  </Table>
  <Table Name="dbo.TPS_Report" Member="TPS_Report">
    <Type Name="LinqExample.Core.TpsReport">
      <!-- snip -->
      <Association Name="FK_TPS_Report_Person" Member="Reporter" Storage="_reporter" ThisKey="ReporterId" OtherKey="Id" IsForeignKey="true" />

Alright, now that everything is ready, add a couple of rows to the TPS_Report table and alter the UI to iterate through each person's reports:

foreach (Person p in Person.LoadAll()) {
    System.Console.WriteLine("{0} {1} is a person here.", p.FirstName, p.LastName);
    foreach (TpsReport report in p.Reports) {
        System.Console.WriteLine("\t and is responsible for the {0} report.", report.Name);
    }
}
System.Console.ReadLine();

Now run the thing!

It's not displaying any of the reports! If you debug, you will see that each person has 0 reports. Wha happen?

The problem is that LINQ to SQL does not understand what our underlying type is for the associations. Unfortunately, there does not appear to be any way to "force" it to recognize that List<TpsReport> really does map properly to the foreign key. I have found two workarounds, but neither are an ideal solution.

The first is to alter our ILoader interface, and have the Person lazy load it's reports:

internal interface ILoader {
  /* snip */
  List<TpsReport> LoadReportsForPerson(Person p);
}

public partial class Person {
  /* snip */
  public List<TpsReport> Reports {
    get { 
      if (this._reports == null) { 
        this._reports = Config.Settings.DataLoader.LoadReportsForPerson(this);
      }
      return this._reports;
    }
  }
  /* snip */
}

public partial class TpsReport {
  /* snip */
  public Person Reporter {
    get {
      if (this._reporter == null) {
        this._reporter = Person.LoadSingle(this.ReporterId);
      }
      return this._reporter;
    }
  }
  /* snip */
}

public class LinqLoader : ILoader {
  /* snip */
  public List<TpsReport> LoadReportsForPerson(Person p) {
    var r = from reports in context.GetTable<TpsReport>()
            where reports.ReporterId == p.Id
            select reports;
    return r.ToList();
  }
  /* snip */
}

This will work well enough, however, things start to get really messy when you add more and more classes. Your Loader will need to add at least one method for each relation you have. Additionally, another potential refactoring would be to have a generic ILoader<T> that does some basic functionality for each type we need. This solution to the problem does not lend itself very well to that model.

The other way I found isn't quite as pretty, and requires the Core library to reference System.Data.Linq. It doesn't outright tie us back to a LINQ To SQL backend, but it does blur the line more than I would like.

As stated, the LINQ to SQL binding does not recognize the List<T> type, but it does recognize the special EntitySet<T> and EntityRef<T> class. EntitySet is used for enumerable objects (like a List), and EntityRef for singles. Additionally, they provide the lazy loading already baked in.

These need to be handled a little bit differently than standard objects, since they are essentially containers for other objects, they need to be instantiated before the contained object is assigned:

private EntitySet<TpsReport> _reports;

public Person() {
  this._reports = new EntitySet<TpsReport>();
}

I've already changed the type of the variable, and I was worried that I would need to change the types for both the properties as well à la:

private EntitySet<TpsReport> _reports;

public EntitySet<TpsReport> Reports {
  get { return this._reports; }
}

However, this turned out to be unnecessary. Only the private variable needs to have it's type changed - the property can still remain List<T>:

private EntitySet<TpsReport> _reports;

public List<TpsReport> Reports {
  get { return this._reports.ToList(); }
  private set { this._reports.Assign(value); }
}

The TpsReport class is slightly different since it uses EntityRef:

public class TpsReport {
  private EntityRef<Person> _reporter;

  public TpsReport() {
    this._reporter = new EntityRef<Person>();
  }

  public Person Reporter {
    get { return this._reporter.Entity; }
    set { this._reporter.Entity = value; }
  }
}

Thanks to the miracles of encapsulation, the signatures of everything remain the same. The only difference is that you do have the extra reference to System.Data.Linq. Admittedly, it's not a perfect solution, but it doesn't require very much extra code, and doesn't create any breaking changes.

As for the big question - does it work? Run it and find out:

Hooray. This is all well and good, but most applications don't just present data, they also store it. Next time we'll learn about actually creating/saving/deleting data.

Source code for this post is located here.