I want my foreign key to be mapped to a single object and not a collection

Say you have two tables:  Teams and Registrations

Registration has a foreign key for Teams :

image

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.

Incoming search terms:

  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1823 jVXuKArkQbKPR_ezvobXRQlOPZEECtUWcwubNc1fgDcVo3RUKKyn6VlCA7YW413z b34ae103fc587512422d9e087fc4ca64c2301ae8&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1836 6EAvZPb89hnB3Z1snWwso4VkTqRjN-r7Ee_gL6hEf8W3R5rxhW8f3syjZzFdDnfE 59baa3356e08abd6bb9b5ed8e51f94d02ef0389a&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1834 Z66pYPggjkYvDchphuytjjdAvTXhaBtXsqzp8u2j8hbD65jL_ITqS-GB5PUXClu7 bf0f92ee0e12d119a46fc667d096881f53e81a9e&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1833 EoK9_v1dd399LVWAKgK5Ar4Az_6wVz4mPGUpbGU3aSo1EOrCCBsFSvakB9KxMO3e f8d57c780f2813b104c9c23e578b0f84ec0fb75c&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1832 K8eEUN4lkeVRfN7XyjvXFGsIWGqnYeFhbG43g-rn3rzLi6CAVCiQZxVu8cYdmcze 2c3b16a7983f2e6966d1c0013d5fb0f065ba5fed&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1830 qt4TKkrgpxMj8sDVdoC5eapiXRmOwSPpc2ud5i0MkyluomUV3DSsnKikSY1RE6Zz 0c1d280eeab4894ba870d1148e9189f973845e2b&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1829 e30lQrNGG1zbQk0ol2Tu5NghQDd7JqTr8hP3kdO7EDgnTx5VpyMvVWVBMDY822mR ad1cdfadd3d44bf76872a9a8bda1e2def3b740d0&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1828 sNRhv71b3CzvQGslrcKoED644tSnSlnr6xntJ_dqqCTAlCOjCtlGqHOm2ScmqznW 9516bc8aa8d602c5ae98eccefd9bb8288f346f89&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1826 EHWVf2tf4pxmuIPk6D4F79FURnmEn2ykip4ilGvaMSDziR52Xhrnt6ZST6ukyaDT 5319b5e8a536700310ee1645a98e2232055d55cd&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1837 tCu8UIhhhyxrhPmmHuk95bM3eNOI4XtCU8gx93tNNYst6SBp95NzHRZvlpwGK0Jn 1f19594216d9b4cfe007cf36c762d4d6dde2edb9&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme

About Sam Beauvois

Application Developer, .NET enthusiast since 2004, I'm interested in technology watch, usability, code quality, patterns & practices, UX, ...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>