Say you have two tables:  Teams and Registrations

Registration has a foreign key for Teams :

In Subsonic 3, the Registration class will have a property called “Beach_Teams” which returns a IQueryable<Beach_Team> collection.

If we look at the generate class in the activerecord.cs file, there is a “Foreign Keys” region which contains the Beach_Teams property:

 #region ' Foreign Keys '
 public IQueryable<Beach_Team> Beach_Teams
 {
   get
   {
     var repo=LJR.DAL.Activities.Beach.Beach_Team.GetRepo();
     return from items in repo.GetAll()
          where items.TeamID == _TeamIDfk
          select items;
   }
 }

#endregion

It’s not ok for my use, I want a property returning only one “Beach_Team” object, so I can use it in my presentation layer with databound controls with no efforts

<asp:ListView runat="server" ID="LastTeams" DataSourceID="registrationsOds">
   <ItemTemplate>
     <%# Eval("Beach_Team.Name")%>
   </ItemTemplate>
 </asp:ListView>

So, what can I do ?

I can edit the ActiveRecord.cs file and add a property called “Beach_Team” and returning a Beach_Team object

 public Beach_Team Beach_Team
 {
   get
   {
     var repo = LJR.DAL.Activities.Beach.Beach_Team.GetRepo();
    return repo.GetByKey(_TeamIDfk);
   }
 }

It do the job !

But if I want to regenerate the ActiveRecord.cs file, my modification will be lost !

So the good solution is to modify the T4 template to generate this property.

Let’s do this :

Go to the ActiveRecord.tt file and search for ‘ Foreign Keys ‘.

Once you have find it look how it works:

#region ' Foreign Keys '
<#
 List<string> fkCreated = new List<string>();
 foreach(FKTable fk in tbl.FKTables)
 {
   if(IsTableOkToBeIncluded(fk.OtherTable))
   {
    string propName=fk.OtherQueryable;
    if(fkCreated.Contains(propName))
    {
      propName=fk.OtherQueryable+fkCreated.Count.ToString();
    }
    fkCreated.Add(fk.OtherQueryable);
#>
   public IQueryable<<#=fk.OtherClass #>> <#=propName #>
  {
  get
  {
     var repo=<#=Namespace #>.<#=fk.OtherClass#>.GetRepo();
     return from items in repo.GetAll()
         where items.<#=CleanUp(fk.OtherColumn)#> == _<#=CleanUp(fk.ThisColumn)#>
         select items;
   }
 }

<#
 }
 }
#>
 #endregion

In a nutshell, for each tables linked to the table we are on, we check if the access code to the table has to be generated (see my previous article : Subsonic : Specify the tables you really need ! ).

If the code has to be generated, we create a string variable named “propName” which will be the name of the property.

If this property already exists, we suffix the name to be sure we don’t have the same property twice.

Then we generate the IQueryable property.

Now, the first thing we want is to have the property name equals to the object name.

We could think that we don’t have to twist our minds because  we already have it with “fk.OtherClass” and i could be like that

 public <#=fk.OtherClass #> <#=fk.OtherClass #>
 {
  get
  {
   var repo=<#=Namespace #>.<#=fk.OtherClass#>.GetRepo();
   return repo.GetByKey(_<#=CleanUp(fk.ThisColumn)#>);
  }
 }

But what if we have two foreign keys to the same table ? We will have the same property twice! and we have to avoid it!

So, we have to check if this property isn’t already generated.

To do this, we can use the list “fkCreated” already present in the code, and create a “secondPropName” string variable, assign it the value of the OtherClass and check if it’s not already created :

 string secondPropName=fk.OtherClass;
 if(fkCreated.Contains(secondPropName))
 {
   secondPropName+=fkCreated.Count.ToString();
 }
 fkCreated.Add(secondPropName);

There is the final code

#region ' Foreign Keys '
<#
 List<string> fkCreated = new List<string>();
 foreach(FKTable fk in tbl.FKTables)
 {
   if(IsTableOkToBeIncluded(fk.OtherTable))
   {
    string propName=fk.OtherQueryable;
    if(fkCreated.Contains(propName))
    {
     propName=fk.OtherQueryable+fkCreated.Count.ToString();
    }
   fkCreated.Add(fk.OtherQueryable);

   string secondPropName=fk.OtherClass;
   if(fkCreated.Contains(secondPropName))
   {
     secondPropName+=fkCreated.Count.ToString();
   }
   fkCreated.Add(secondPropName);
#>
  public IQueryable<<#=fk.OtherClass #>> <#=propName #>
  {
    get
    {
      var repo=<#=Namespace #>.<#=fk.OtherClass#>.GetRepo();
      return from items in repo.GetAll()
        where items.<#=CleanUp(fk.OtherColumn)#> == _<#=CleanUp(fk.ThisColumn)#>
        select items;
    }
  }

  public <#=fk.OtherClass #> <#=secondPropName #>
  {
    get
    {
      var repo=<#=Namespace #>.<#=fk.OtherClass#>.GetRepo();
      return repo.GetByKey(_<#=CleanUp(fk.ThisColumn)#>);
    }
  }

<#
 }
 }
#>
 #endregion

Now I can use the linked object directly !

You can donwload my T4 templates here.