Joe Stevens' BlogEverything .NET and C#

Consider the following:

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])
    )

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:

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 the rest of this entry »

I’ve been playing around with the ListView control recently and am quite impressed with it.  I like how it gives full control over the markup used as apposed to the other data driven controls.

I came across an issue where in my ItemTemplate I have a CheckBoxList which I needed to reference when I hit a button also in the ItemTemplate.

As the ItemTemplate repeats there will be many CheckBoxLists so how do I find the correct one?

Read the rest of this entry »

Following on from my last post creating an extension method to use FindControl recursively I thought it would be cool to also have a TryFindControl method which works in a similar way to Int32.TryParse which I use regularly.

Below is a generic extension method that uses my previously created recursive FindControl method to cast the object to the correct type if found and send it back as an out parameter, then return a boolean indicating if the control was found.

public static bool TryFindControl<T>(this Control ctrl, string id, out T control, bool recursive) where T : class
{
    Control ctrlFound = ctrl.FindControl(id, recursive);
    control = ctrlFound as T;
    return ctrlFound is T;
}

The method could then be used like so.

TextBox txt = null;
if (this.TryFindControl<TextBox>("txtName", out txt, true))
{
    txt.Text = "Joe Stevens";
}

When working with templated controls it’s quite common to need a recursive FindControl method to navigate down through the control hierarchy to find the control you want.

Here is a simple extension method I wrote for Control to do this:

public static Control FindControl(this Control ctrl, string id, bool recursive)
{
    if (recursive)
    {
        foreach (Control child in ctrl.Controls)
        {
            if (child.ID != null && child.ID.Equals(id))
            {
                return child;
            }

            if (child.Controls.Count > 0)
            {
                Control found = FindControl(child, id, true);
                if (found != null)
                {
                    return found;
                }
            }
        }
    }
    else
    {
        return ctrl.FindControl(id);
    }

    return null;
}

I’ve been working with the CheckBoxList control and it’s annoying there is no property to get the selected items.

I wrote a simple extension method for ListControl, the abstract class that CheckBoxList and ListBox derive from, to get the selected items:

public static ListItemCollection GetSelectedItems(this ListControl source)
{
    ListItemCollection selectedItems = new ListItemCollection();

    foreach (ListItem item in source.Items)
    {
        if (item.Selected)
        {
            selectedItems.Add(item);
        }
    }

    return selectedItems;
}

I could loop through the selected items like so:

foreach (ListItem item in myCheckBoxList.GetSelectedItems())
{
    //Do something with the item
}

It would be even better if Microsoft implemented extension properties so that I can loose the parenthesis.

I was using the ObjectDataSource today to display and delete data using a GridView.  When I tried to delete a record that was referenced in another table an exception was thrown due to referential integrity.

I found that in implementing the Deleted event of the ObjectDataSource, the ObjectDataSourceStatusEventArgs class had a boolean property called ExceptionHandled.  Setting this to true stops the ObjectDataSource from re-throwing the exception, allowing you to handle it yourself.  You can obtain the underlying exception using the Exception property.

protected void odsUser_Deleted(object sender, ObjectDataSourceStatusEventArgs e)
{
    if (e.Exception != null)
    {
        e.ExceptionHandled = true;
        //Perform own exception handling
        lblError.Text = e.Exception.Message;
    }
}

You could also use the same approach with the Updated event.

A few years ago I wrote a custom parameter for data sources such as the ObjectDataSource which used the value of a property on the page for a parameter.

I needed this functionality again so I found what I had previously written and improved it to make it more usable.

Read the rest of this entry »