Using KnockoutJS with SignalR in ASP.NET MVC

KnockoutJS is a MVVM implementation for JavaScript written by Steve Sanderson, in my opinion the author of the best ASP.NET MVC textbooks available. Simply put it lets you bind a JavaScript object model to your HTML UI using a Read more

A MongoDB Tutorial using C# and ASP.NET MVC

In this post I'm going to create a simple ASP.NET MVC website for a simple blog that uses MongoDB and the offical 10gen C# driver. MongoDB is no NOSQL database that stores information as Binary JSON (BSON) in documents. I Read more

Linq To SQL Tutorial

Check out some of my other Linq to SQL posts: EntityBase Inheritance Modifiers with SQLMetal Linq to SQL with WCF Services Linq to SQL Framework (Repository/Business wrapper) ObjectDataSource binding with server side paging and sorting Load Options Generic Framework using reflection This is a basic tutorial for Read more

Linq lambda expression IEqualityComparer for IEnumerable.Distinct and Except

Posted on by Joe in C#, Linq | 3 Comments

One of the things that annoys me with the IEnumerable.Distinct method is that it has no overload allowing you to give a lambda expression to specify a particular property to perform the distinction, you have to give an IEqualityComparer.

I did a quick Google search and found this post. The guy here provides the following LambdaComparer class:

Read more

Convert a comma separated string of numbers to an integer array using C#

Posted on by Joe in C#, Linq | 1 Comment

Today I needed to convert a comma separated string of numbers in an integer array. Here is how you can do it in one line using Linq:

string csv = "1,1,2,3,5,8,13,21,34";
int[] numbers = csv.Split(',').Select(n => int.Parse(n)).ToArray();

Linq to XML Tutorial

Posted on by Joe in ASP.NET, C#, Linq, XML | 45 Comments

Download source

This is an introduction to Linq to XML showing how to read, insert, update and delete from an XML file.

Read more

Linq to SQL Tutorial – Linq to SQL Generic Framework using reflection

Posted on by Joe in ASP.NET, C#, Linq | 1 Comment

Download source code

In a previous post; Base Repository/Business Logic wrapper, I talked about a basic Linq to SQL Framework I created. I then extended it in my ObjectDataSource binding with paging and sorting post to show how to use it with the ObjectDataSource.

The problem was that although it worked quite nicely for getting all records and getting by ID, if I wanted to perform any specific filtering I had to create a derived repository class with a specific method for each query.

In this post I’ll explain how I have extended this framework using reflection to allow filtering on multiple columns without having to created separate repository objects.

Read more

Linq to SQL Tutorial – Using Load Options to preload data immediately without lazy loading

Posted on by Joe in ASP.NET, C#, Linq | 1 Comment

Download source

With Linq to SQL lazy loading is used by default. That means that if one object contains another object, the child object will only be loaded when first accessing it. Using Load Options it is possible to tell Linq to SQL to also load the child object at the same time as loading it’s parent.

Read more

Linq Tutorial – Using the Enumerable.Any extension method

Posted on by Joe in ASP.NET, C#, Linq | Leave a comment

Consider the following:

Entities

Using Linq I want to select all the roles for a particular user. I could create a query like this:

 

public List<Role> GetByUserID(int userID)
{
    List<Role> roles = (from r in Context.Roles
                       join ur in Context.UserRoles on r.ID equals ur.RoleID
                       where ur.UserID.Equals(userID)
                       select r).ToList();

    return roles;

}

This simply creates the following SQL:

SELECT [t0].[RoleID] AS [ID], [t0].[Name]
FROM [dbo].[Role] AS [t0]
INNER JOIN [dbo].[UserRole] AS [t1] ON [t0].[RoleID] = [t1].[RoleID]
WHERE [t1].[UserID] = @p0

Alternatively I could use the Any extension method.

public List<Role> GetByUserID(int userID)
{
    return Context.Roles.Where(r => r.UserRoles.Any<UserRole>(ur => ur.UserID.Equals(userID))).ToList();
}

The Any method returns a bool which indicates if any of the UserRoles meet the lambda expression; in this case checking the UserID property. The Where method evaluates the Roles against the Roles the User belongs to so we only get those Roles.

The resulting SQL looks like this:

SELECT [t0].[RoleID] AS [ID], [t0].[Name]
FROM [dbo].[Role] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[UserRole] AS [t1]
    WHERE ([t1].[UserID] = @p0) AND ([t1].[RoleID] = [t0].[RoleID])
    )

Linq to SQL – Using EntitySet.Remove to delete records

Posted on by Joe in ASP.NET, C#, Linq | 3 Comments

Download source

Quite often you have two tables with a linker table which causes Linq to SQL to generate an EntitySet of the related records in each table.

Take the following example:

Entities

Here, the User entity will have an EntitySet called UserRoles containing the Role entities that the user is part of.

When I want to remove a role from a user what I’d like to do is the following:

user.UserRoles.Remove(userRole);

Unfortunately after doing this, when I try to submit the changes to the database I get the following error:

“An attempt was made to remove a relationship between a User and a UserRole. However, one of the relationship’s foreign keys (UserRole.UserID) cannot be set to null.”

Read more

Linq to SQL Tutorial – ObjectDataSource binding with paging and sorting

Posted on by Joe in C#, Linq | 5 Comments

Update: 31/10/2009 – Added source code

ASP.Net has the LinqDataSource which is a handy control but it embeds the business logic in the page which I don’t like. In this post am I going to use the repository objects I created in my last post to bind a GridView using an ObjectDataSource enabling filtering, sorting, and server side paging.

I have a GridView control with an ObjectDataSource as the object. Below are the properties for my ObjectDataSource:

<asp:ObjectDataSource
    ID="odsStudent"
    runat="server"
    TypeName="Repository.StudentRepository"
    DataObjectTypeName="Entities.Student"
    SelectMethod="GetAll"
    UpdateMethod="Save"
    DeleteMethod="Delete">
</asp:ObjectDataSource>

Here we have the following:

  • TypeName – The object, here this is the repository.
  • DataObjectTypeName – The entity, here this is Student. This tells the ObjectDataSource to pass an instance of Student when inserting, updating or deleting instead of the separate values.
  • SelectMethod – The method to load the data, here this is GetData from the base repository.
  • UpdateMethod – The method to update the data, here this is Save from the base repository.
  • DeleteMethod – The method to delete the data, here this is Delete from the base repository.

Running the application at this point would correctly load the data, but delete would not work and update would insert duplicate records. This is because Linq to SQL doesn’t know which entity to attach. To resolve this I can set the DataKeyNames property of the GridView to include the timestamp column:

DataKeyNames="ID, UpdateDate"

I talk more about timestamp columns in my Linq to SQL with WCF Services post.

Now that this property has been added updates and deletes work fine within the GridView. To add server side paging I need to modify my repository to create an overload for GetAll. In doing this I have also added a protected method in the base repository called PageFilter:

protected List<T> PageFilter(IQueryable<T> entities, int startRow, int maxRows)
{
    return entities.Skip(startRow).Take(maxRows).ToList();
}

This method return a List containing the rows from IQueryable between startRow and maxRows. My overloaded GetAll method accepts startRow and maxRows and uses the PageFilter method:

public List<T> GetAll(int startRow, int maxRows)
{
   return PageFilter(_context.GetTable<T>(), startRow, maxRows);
}

The reason for separating the PageFilter is for reuse within the base class, and derived classes which I will demonstrate later. Now that I have my new GetAll method I need to add some new properties on my ObjectDataSource:

SelectCountMethod="Count"
StartRowIndexParameterName="startRow"
MaximumRowsParameterName="maxRows"
EnablePaging="true"
  • SelectCountMethod – Used to determine the number of pages, here this is Count method in the base repository.
  • StartRowIndexParameterName – The name of the parameter for the start row index, this needs to match the name of the parameter in the overloaded GetAll method in the base repository.
  • MaximumRowsParameterName – The name of the parameter for the maximum rows, this needs to match the name of the parameter in the overloaded GetAll method in the base repository.
  • EnablePaging – This needs to be set to true to enable server side paging, otherwise the paging will still be done at the client.

Note: If you configure the ObjectDataSource through the UI and select the new GetAll method it will add parameters to the datasource. We don’t want this so select the parameterless overload or set up the ObjectDataSource in the markup.

When I run the application it now works using server side paging without really putting in too much work. Next I want to add sorting. To do this I need a new overloaded method for PageFilter and another one for GetAll which accepts the parameters for paging and sorting:


protected List<T> PageFilter(IQueryable<T> entities, int startRow, int maxRows, string orderBy)
{
    if (string.IsNullOrEmpty(orderBy))
    {
        orderBy = "ID";
    }

    return entities.OrderBy(orderBy).Skip(startRow).Take(maxRows).ToList();
}

You may notice I’m using the string variable orderBy as a parameter for the OrderBy method which would not usually be allowed. This is because I have included the Linq Dynamic Library in my project. Scott Guthrie has a post on this here. To use the new dynamic extension methods I have added the Dynamic.cs file to my project and added a reference to System.Linq.Dynamic. I am also checking if orderBy is null or empty and setting a default if so. This is because the dynamic library will throw an exception if it is passed an empty string. ID is fine to use as a default as I know all of my entities will have an ID property.

The new GetAll method looks like this:

public List<T> GetAll(int startRow, int maxRows, string orderBy)
{
    return PageFilter(_context.GetTable<T>(), startRow, maxRows, orderBy);
}

This method takes in the paging and sorting parameters and uses the new PageFilter method. Now the methods have been set up I need to set another property on the ObjectDataSource:

SortParameterName="orderBy"
  • SortParameterName – The parameter name in the select method that will be used for sorting, in this case orderBy.

Now I have binding, updating, deleting, sorting and server side paging all working nicely with very little code! Next I am going to add some simple filtering to filter the results on Forename and Surname. I have added the following to my page:

<fieldset>
    <legend>Search</legend>
    Forename: <asp:TextBox ID="txtSForename" runat="server" />
    Surname: <asp:TextBox ID="txtSSurname" runat="server" />
    <br />
    <asp:Button ID="btnSearch" Text="Search" runat="server" onclick="btnSearch_Click" />
    <asp:Button ID="btnClear" Text="Clear" runat="server" onclick="btnClear_Click" />
</fieldset>

The search button simply resets the GridView’s PageIndex so that it forces it to page 1 after a search, and the Clear button results the values in the TextBoxes:


protected void btnSearch_Click(object sender, EventArgs e)
{
    gvStudents.PageIndex = 0;
}

protected void btnClear_Click(object sender, EventArgs e)
{
    txtSForename.Text = string.Empty;
    txtSSurname.Text = string.Empty;
}

As the Search is student specific I need to add some new functionality to the student repository to handle the search. Firstly I have added a private method called StudentSearch which actually does the filtering and returns IQueryable<Student> accepting parameters for forename and surname:


private IQueryable<Student> StudentSearch(string forename, string surname)
{
    var query = from s in Context.Students
                select s;

    if (!string.IsNullOrEmpty(forename))
    {
        query = query.Where(s => s.Forename.Equals(forename));
    }

    if (!string.IsNullOrEmpty(surname))
    {
        query = query.Where(s => s.Surname.Equals(surname));
    }

    return query;
}

I have also added three different overloads for a public method called search. These all use the private StudentSearch method to handle filtering with no paging, with paging, and paging with sorting:


public List<Student> Search(string forename, string surname)
{
    return StudentSearch(forename, surname).ToList();
}

public List<Student> Search(string forename, string surname, int startRow, int maxRows)
{
    return PageFilter(StudentSearch(forename, surname), startRow, maxRows);
}

public List<Student> Search(string forename, string surname, int startRow, int maxRows, string orderBy)
{
    return PageFilter(StudentSearch(forename, surname), startRow, maxRows, orderBy);
}

I now need to change my ObjectDataSource’s SelectMethod to Search and add the SelectParameters for Forename and Surname:

<asp:ObjectDataSource
    ID="odsStudent"
    runat="server"
    TypeName="Repository.StudentRepository"
    DataObjectTypeName="Entities.Student"
    SelectMethod="Search"
    UpdateMethod="Save"
    DeleteMethod="Delete"
    SelectCountMethod="Count"
    StartRowIndexParameterName="startRow"
    MaximumRowsParameterName="maxRows"
    SortParameterName="orderBy"
    EnablePaging="true">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtSForename" DbType="String" Name="forename" />
        <asp:ControlParameter ControlID="txtSSurname" DbType="String" Name="surname" />
     </SelectParameters>
</asp:ObjectDataSource>

Running the application now throws the following error:

ObjectDataSource ‘odsStudent’ could not find a non-generic method ‘Count’ that has parameters: forename, surname.

This is because the ObjectDataSource is now expecting a Count method with forename and surname parameters to get the number of filtered records. To overcome this I have added a Count method to the student repository which takes in this arguments and uses the StudentSearch method to return the number of records:

public int Count(string forename, string surname)
{
    return StudentSearch(forename, surname).Count();
}

Now everything works! Updating, deleting, server side paging, ordering and filtering witch very little code at the page level. I am sure I will come up with a more generic way to perform filtering but for now this way is fairly straight forward and doesn’t require too much code to implement.

Linq to SQL Tutorial – Base Repository/Business Logic wrapper

Posted on by Joe in C#, Linq | 2 Comments

Before Linq to SQL I always separated out my Entities, Data Access Layer and Business Logic Layer.  With Linq to SQL it’s a little different as I feel the generated classes are kind of like the Entity and Data Access Layer combined.  Rather than using the generated entities directly it is still good practice to have a Business Logic Layer.  In this example I am using the term Repository which is an aspect of Domain-Driven Design.

Each entity will likely need it’s own repository object, as there will business logic that is specific for that entity, but there will be logic that is shared between all entities so it is worth creating a base object for this.

I have created a generic abstract class called RepositoryBase which has a constraint for EntityBase which is the base entity class I created in my Set inheritance modifiers with SQLMetalpost.  This ensures that the repositories can only be used with the objects created by Linq to SQL.

To handle my DataContext I am creating an instance of it in the constructor and keeping this instance for the life of the object.  For this reason my base repository implements IDisposable to handle disposing the DataContext.

Here is the code for my base repository:


public abstract class RepositoryBase&lt;T&gt; : IDisposable where T : EntityBase
{
    private UniversityDataContext _context;

    public RepositoryBase()
    {
        _context = new UniversityDataContext(ConfigurationManager.ConnectionStrings[&quot;UniversityConnectionString&quot;].ConnectionString);
    }

    public UniversityDataContext Context
    {
        get
        {
            return _context;
        }
    }

    public List&lt;T&gt; GetAll()
    {
        return _context.GetTable&lt;T&gt;().ToList();
    }
    public T GetByID(int id)
    {
        return _context.GetTable&lt;T&gt;().FirstOrDefault(e =&gt; e.ID.Equals(id));
    }

    public int Count()
    {
        return _context.GetTable&lt;T&gt;().Count();
    }

    public void Save(T entity)
    {
        if (entity.IsNew)
        {
            _context.GetTable&lt;T&gt;().InsertOnSubmit(entity);
        }
        else
        {
            _context.GetTable&lt;T&gt;().Attach(entity, true);
        }

        _context.SubmitChanges();
    }

    public void Delete(T entity)
    {
        if (!entity.IsNew)
        {
            _context.GetTable&lt;T&gt;().Attach(entity);
            _context.GetTable&lt;T&gt;().DeleteOnSubmit(entity);
            _context.SubmitChanges();
        }
    }

    public void Dispose()
    {
        if (_context != null)
        {
            _context.Dispose();
        }
    }
}

You can see it contains methods that are relevant for all entities.  A method to get all entities, a method to get all entities by ID which I can do due to ID being a virtual member in my EntityBase class which is overridden by each entity.  It also has generic methods for saving and deleting which will work for any of the entities.  There will be a lot more logic that could go into this base class, but for now this just shows the basics.

Using this class you can derive separate repositories for each entity. I have a public property for the Context which allows any derived classes to use the DataContext instance.  Here is my implementation for StudentRepository which adds a couple of student specific methods:


public class StudentRepository : RepositoryBase&lt;Student&gt;
{
    public List&lt;Student&gt; GetByForename(string forename)
    {
        return Context.Students.Where(s =&gt; s.Forename.Equals(forename, StringComparison.CurrentCultureIgnoreCase)).ToList();
    }

    public List&lt;Student&gt; GetBySurname(string surname)
     {
        return Context.Students.Where(s =&gt; s.Surname.Equals(surname, StringComparison.CurrentCultureIgnoreCase)).ToList();
    }
}

Using this derived class I could use any of the base methods:

StudentRepository rep = new StudentRepository();
Student s = rep.GetByID(1);
s.Forename = &quot;Joseph&quot;;
rep.Save(s);
gvStudents.DataSource = new StudentRepository().GetAll();
gvStudents.DataBind();

I could also use any of the entity specific methods:

StudentRepository rep = new StudentRepository();
List&lt;Student&gt; students = rep.GetByForename(&quot;Joseph&quot;);
foreach (Student s in students)
{
    rep.Delete(s);
}

To explicitly dispose of the object and the underlying DataContext I could do the following:

using (StudentRepository rep = new StudentRepository())
{
    List&lt;Student&gt; students = rep.GetByForename(&quot;Joseph&quot;);
    foreach (Student s in students)
    {
        rep.Delete(s);
    }
}

Linq to SQL with WCF Services

Posted on by Joe in Linq, Silverlight, WCF | 5 Comments

I was interested to see how I could use Linq to SQL with WCF Services to load and save data using a Silverlight project.  In this post I will expand upon the database I created in my Linq to SQL Tutorial and the console application I wrote for my Set inheritance modifiers with SQLMetal post.

The first step is to enable serialisation on my Linq entities so that they can be sent over the wire. To do this in the O/R Designer you can select the white space of the designer and view the DataContext properties.  Set the property called Serialization Mode to Unidirectional:

Linq Serialization Mode Property

 

If using SQLMetal you can use the serialization command line argument:

SQLMetal.exe"/server:localhost /database:University /dbml:University.dbml <strong>/serialization:unidirectional</strong> /namespace:Entities /context:UniversityDataContext /pluralize

Enabling unidirectional serialization in either of these two ways adds the necessary DataContract and DataMember attributes to the generated entities and properties:

[Table(Name="dbo.Student")]
[DataContract()]
public partial class Student : EntityBase, INotifyPropertyChanging, INotifyPropertyChanged
{
    ....

    [Column(Storage="_Forename", DbType="NVarChar(50) NOT NULL", CanBeNull=false, UpdateCheck=UpdateCheck.Never)]
    [DataMember(Order=3)]
    public string Forename
    {
        ....
    }
}

The entities are now in a state where they can be serialised and sent down the wire.  In my WCF service I have a method that returns a list of my Linq to SQL Student entity:

public List GetStudents()
{
    using (_context)
    {
        return _context.Students.ToList();
    }
}

These entities can then be easily used by the client, in this case the Silverlight application:


UniversityContractClient _proxy = new UniversityContractClient();

private void PopulateStudents()
{
    _proxy.GetStudentsCompleted += new EventHandler(proxy_GetStudentsCompleted);
    _proxy.GetStudentsAsync();
}

void proxy_GetStudentsCompleted(object sender, GetStudentsCompletedEventArgs e)
{
    dgStudents.ItemsSource = e.Result;
}

Here I am using the list to populate a DataGrid:

Linq WCF Datagrid

This is all very straight forward, but the next step to update the data it a little more complex.  Here is my service method to save a Student entity created or updated by the client:

public void SaveStudent(Student student)
{
    using (_context)
    {
        if (student.IsNew)
        {
            _context.Students.InsertOnSubmit(student);
        }
        else
        {
            _context.Students.Attach(student, true);
        }
               
        _context.SubmitChanges();
    }
}

Here I am using the IsNew property I created in my Set inheritance modifiers with SQLMetal post to check if the entity is to be inserted or updated.  The insert code is simple enough, but for the update we have to attach the entity to the DataContext as it has been modified outside of the DataContext’s scope.  I’m at doing this using the Attach method of the Student table, passing true for the asModified parameter to state that the entity has been updated.

In my Silverlight application I have a DataForm which calls this method passing the updated Student entity:

Linq WCF DataForm

At this point inserting data will work, but when I try to update an entity the service method will throw the following error when trying to attach the entity:

An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

This occurs because the entity was modified outside of the scope of the DataContext, so Linq to SQL doesn’t know what has changed about the entity and what to update.  To overcome this we can use a Timestamp column.  The Timestamp is a byte array which is used for versioning.  Linq to SQL knows to check this column to see if an object has been updated.  In my database I have changed the Student table so that it has a field called Timestamp, of type timestamp which doesn’t allow NULLs:

Linq WCF Timestamp Field

When adding the new column, the O/R Designer automatically knows this is a timetamp column and sets the Time Stamp and Auto Generated Value properties to true:

Linq WCF Timestamp Properties

 

SQLMetal will also detect a column with the timestamp type and set the necessary attributes.

With this timestamp column set up it will now be possible to successfully update an entity that was changed by the client.

In my example if I try to update the entity twice it will throw the following exception when trying to submit the changes:

Row not found or changed.

This is because the client doesn’t have the entity with the updated timestamp.  Also when adding a new entity the entity at the client won’t have the updated ID identity column so trying to update this will create another entity.  To resolve this I can change my SaveStudent service method to return the updated Student entity:


public Student SaveStudent(Student student)
{
    using (_context)
    {
        if (student.IsNew)
        {
            _context.Students.InsertOnSubmit(student);
        }
        else
        {
            _context.Students.Attach(student, true);
        }
               
        _context.SubmitChanges();
    }

    return student;
}

In my Silverlight application I then pass the hash code for the object as the userState when calling the asyncronus service method:

_proxy.SaveStudentAsync(student, student.GetHashCode());

This user state can then be obtained in the callback EventArgs class using e.UserState.  Using this I get the correct object from my collection, update it and reassign the source for my DataGrid and DataForm:

void _proxy_SaveStudentCompleted(object sender, SaveStudentCompletedEventArgs e)
{
    ObservableCollection students = (ObservableCollection)dgStudents.ItemsSource;
    Student student = students.Where(s => s.GetHashCode() == Convert.ToInt32(e.UserState)).First();
    if (student.ID == 0)
    {
        student.ID = e.Result.ID;
    }
    student.Timestamp = e.Result.Timestamp;
    dgStudents.ItemsSource = students;
    dfStudent.ItemsSource = students;
}

This is all well and good and works as expected but what I really wanted to do was have an UpdateDate column which holds the date of the last update which could be used as a timestamp.  I replaced my current Timestamp column with an UpdateDate column:

Linq WCF UpdateDate Field

The default for the new column is set to getdate() to automatically populate with the current date when creating a new record:

Linq WCF UpdateDate Default

Using the O/R Designer this field can be set to a timestamp by setting the Time Stamp property to True, which will automatically set Auto Generated Value to True.

As I am using SQLMetal I can update the console application I wrote in my Set inheritance modifiers with SQLMetal post to add an IsVersion attribute to the DBML XML as well as the Modifier attribute:


.... code omitted ....

//Find the column node
if (child.Name.Equals("Column"))
{
    if (child.Attributes["Name"].Value.Equals("ID"))
    {
        //Create the Modifier attribute to add to ID column
        XmlAttribute modifierAttribute = xmlDoc.CreateAttribute("Modifier");
        modifierAttribute.Value = "Override";
        child.Attributes.Append(modifierAttribute);
    }
    else if (child.Attributes["Name"].Value.Equals("UpdateDate"))
    {
        //Create the IsVersion attribute to add to UpdateDate column
        XmlAttribute versionAttribute = xmlDoc.CreateAttribute("IsVersion");
        versionAttribute.Value = "True";
        child.Attributes.Append(versionAttribute);
    }
}

.... code omitted ....

Doing this adds the following values to the Column attribute on the UpdateDate property in the Student entity.  You can see IsVersion=true which tells Linq to SQL this property is the timestamp.

[Column(Storage="_UpdateDate", AutoSync=AutoSync.Always, DbType="DateTime NOT NULL", IsDbGenerated=true, IsVersion=true, UpdateCheck=UpdateCheck.Never)]

At this point everything works okay, but the UpdateDate is not refreshed on update.  To fix this add a trigger that sets the date on update:

ALTER TRIGGER trg_UpdateDate
ON dbo.Student
FOR UPDATE
AS
    UPDATE      Student
    SET         UpdateDate = getdate()
    WHERE       (ID IN (SELECT ID FROM Inserted))

The UpdateDate is now set for each update and is used by Linq to SQL as the timestamp.