Automatic Update of Datamodel In Linq

One of the issues that we came across fairly quickly when converting some projects to Linq was how unusable the default Linq to SQL utility was. The interface worked extremely well for 2 or 3 tables, but quickly became unmanageable with much more than that.

We began looking for other solutions, and discovered a command line utility called SQLMetal that can be used to generate a DataModel from a connection string and/or an XML file.

The solution we settled on uses SQLMetal to generate XML markup from the database, then uses XSL Transformations to make desired property/model changes (Private properties, Delay loaded, etc), and then uses SQLMetal to generate a code file from this XML file.

To start, we created a batch file called updateModel.bat and placed it in the project:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.exe" /conn:"CONNECTIONSTRING" 
      /timeout:0 /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp /pluralize 
      /dbml:"%~dp0DataModel.dbml"
"%~msxsl.exe" "%~dp0DataModel.dbml" "%~dp0ModifyDbml.xslt" -o "%~dp0DataModel.dbml"
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.exe" /code:"%~dp0DataModel.cs"
    /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp 
    /pluralize %DataModel.dbml

The output of SQLMetal in the first line of this file is an XML file called DataModel.dbml that looks something like this : 
  <Table Name="dbo.Person" Member="Persons">
    <Type Name="Person">
      <Column Name="PersonID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false"></Column>
      <Column Name="AddressID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false"></Column>
      <Column Name="Email" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column
      <Column Name="NameFirst" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column>
      <Column Name="NameLast" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column>
<Association Name="FK_Person_Address" Member="Address" ThisKey="AddressID" OtherKey="AddressID" Type="Address" IsForeignKey="true"></Association>
    </Type>
  </Table>

The second line of this script uses a utility called msxsl.exe (note that this requires MSXML). This program uses a file called ModifyDbml.xslt to perform an XSL tranformation on the DataModel.dbml file.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
								xmlns:dbml="http://schemas.microsoft.com/linqtosql/dbml/2007"
								xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
								xmlns:msxsl="urn:schemas-microsoft-com:xslt"
								exclude-result-prefixes="msxsl">
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="dbml:Database/dbml:Table/dbml:Type[@Name = 'Person']/dbml:Column[@Name = 'AddressID']">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()" />
       <xsl:attribute name="AccessModifier">Private</xsl:attribute>
    </xsl:copy>
  </xsl:template>

<xsl:template match="@* | node()">
     <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>


This will make it so the “AddressID” property of “Person” is a private property – and this is where all such DataModel changes should be stored. Note that any changes made directly to the DataModel after this point will be lost each time the files are generated.
The final line of this script generates a DataModel.cs file from the updated XML file.
Finally, we looked for a way to call this script to update from within visual studio. To do this, we went to Tools -> External Tools -> Add, and used the following arguments:

This allows you to highlight the updateModel.bat from Visual Studio and go to “Tools->CMD Prompt” to update your DataModel.
			

			
			

IEnumerable.Flatten in C#

One thing I’ve seen fairly often is this:

public IEnumerable<Relationship> GetRelationships()
{
     var itemRels = (from i in DataContext.Items
                      select i.Relationships);

     var relList = new List<Relationship>();
     foreach (var rels in itemRels) {
          relList = relList.Concat(rels).ToList();
     }

     return relList;
}


This is essentially the same as the “Flatten” function that exists in many other languages, i.e.
var array = [1,2][3][4,5,6].flatten() // array = [1,2,3,4,5,6];
There is a built in extension for selecting objects like this in Linq :
     return Datacontext.Items.SelectMany(i => i.Relationships);


		
	

Bing Maps API – Getting Started

Lanit had recently been researching getting a mapping API set up for use in various projects. Ultimately, due to ease of use and licensing, we settled on the Bing Maps API.

The goal of the first project using these maps was to plot a fairly large number of items with known locations on a simple map of Missouri.

Starting out, you must first include a reference to the bing maps in your page:

<script type='text/javascript' src='http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2&mkt=en-us'/>

After that, create some objects that you’re going to plot. For our implementation, all the needed information is stored on the page -so these properties are just getting values from these.

function Item($container) {
    var item = this;
    var lngBox = $container.find("input.lng");
    var latBox = $container.find("input.lat");
    item.getLat = function() { return latBox.val(); };
    item.getLng = function() { return lngBox.val(); };
    item.Icon = “img/iconhere”;
    item.Title = “Title Here”;
}

Item.prototype.plot = function(map) {
    var item = this;
    item.getPosition(function(latLng) {
    var thisPin = new VEShape(VEShapeType.Pushpin, latLng);
    thisPin.SetCustomIcon(item.Icon);
    thisPin.SetTitle(Item.Title);
    var pinPoint = map.AddShape(thisPin);
});};

It’s worth noting here the “plot” is expecting an object of type “VEMap”, and will then position a point of type VEShape at the position of the item.

For our implementation, we had static addresses with Latitudes and Longitudes already saved. If your implementation will need to geocode addresses, the “GetLat” and “GetLng” functions will need to be replaced with calls to the geocode service.

After creating these objects, we’ll load the map on page load and plot the objects

var settings = {
              mapOpts: {container: "map", // id of map 
             containerheight: 550, 
           width: 600, 
zoom: 7, 
center: new VELatLong(38.208142, -92.381362) },
elementClick: function() { }// this code is run when a plotted item is clicked
};

$(function() {

    $("#" + settings.mapOpts.container).css({
        'width': settings.mapOpts.width, // the API will not automatically set the height/width of the container object – this must be done before the maps are loaded.
        'height': settings.mapOpts.height,
        'position': 'relative'
    });

    // creates a map in the dom object by ID
    var map = new VEMap(settings.mapOpts.container);

    // attach an event to the item clicks -
    map.AttachEvent("onclick", function(e) {
        if (e.elementID != null) { // call the click function using the Pin element
            var shape = map.GetShapeByID(e.elementID);
            settings.elementClick(shape);
        }
    });

    // this renders the map to the page
    map.LoadMap(settings.mapOpts.center, settings.mapOpts.zoom, null, false, null, false);

    // disable zooming on mouse wheel
    map.AttachEvent("onmousewheel", function(e) {
        var mouseWheel = -e.mouseWheelChange / 2;
        window.scrollBy(0,mouseWheel);
        return true;
    });

    // plot the items on the map
    var items = $("#itemContainer").find("div.itemContainer");
    items.each(function() {
        var item = new Item($(this));
        item.plot(map);
   });
});

You can see some implementation decisions here – the pages were pretty unusable with the default functionality to scroll on mouse wheel, so that was disabled.

Linq Expressions on an Interface

One thing I found myself doing quite a bit an often causing logic errors was comparing
dates in Linq queries. For example, if I want to get games that occur between a date range, I might be
doing something like this :

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
				where comp.Date >= startDate && comp.Date <= endDate
				select comp);
	}

I then want to get a list of tournaments that occur between a date range, and I might
essentially rewrite this code using a couple different variables. Rather, I can implement
a common interface on these classes and have an expression applied to all classes that
implement this interface.

	public interface IDate
	{
		DateTime Date { get; set; }
	}
	
	public partial class Competition : DataBase, IDate
	{
		...
	}
	
	public static class Expressions
	{
        public static Expression<Func<T, bool>> GetBetweenDates<T>(DateTime start, DateTime end)
            where T : IDate
        {
            return (t => t.Date >= start && t.Date <= end);
        }	
	}	

Then, rather than rewriting this for each class with a date, I can just apply this expression
to my queries.

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
			select comp).Where(
                             Expressions.GetBetweenDates<Competition>(startDate, endDate));
	}

Using Custom Properties Inside LINQ to SQL Queries

One thing that initially caused me some trouble with Linq to SQL was that properties
and functions defined on the object cannot be translated to SQL. For example, this code throws an
“The member ‘Table1.DisplayName’ has no supported translation to SQL.” exception when executed.

public string DisplayName
   {
      get
      {
         return this.NameFirst + " " + this.NameLast;
      }
}

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.DisplayName);
}

One simple solution is to just define your sort option each time it needs used.

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.NameFirst + " " + t.NameLast);
}

This option is pretty tedious to write and maintain, especially if you’re going to be using the function often.

Another option is to define a System.Linq.Expression, which is a lambda expression that can be converted to SQL.

static Expression<Func<Table1, string>> DisplayNameExpr = t => t.NameFirst + " " + t.NameLast;

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(Table1.DisplayNameExpr);
}

Note that if you do choose this way, the “DisplayName” is essentially defined in two different places. To solve this problem, use the expression to define the property.

{
   get
   {
      var nameFunc = DisplayNameExpr.Compile(); // compile the expression into a Function
      return nameFunc(this); // call the function using the current object
   }
}

Note this method can also be used to define functions that accept arguments,  so the following code also works as expected.

static Expression<Func<Table1, bool>> HasReq(int numReq)
{
   return (t => (t.IntItem + t.AnotherInt) > numReq);
}

public static IQueryable<Table1> GetWithReq(int req)
{
   return (from t in DataContext.Table1s
             select t).Where(Table1.HasReq(req));
}