Returning the row index using LINQ

Imagine you have a contest and each entry has a some points that they were awarded. To show an ordered list of them is easy enough. Something like the following will return the names and the related points.

Entries
.Select(entry => new {
entry.Name,
entry.TotalPoints,
}
)
.OrderBy (o => o.TotalPoints)

Now what if you wanted to show the rank (1st, 2nd, 3rd, etc) for each entry. So basically now we have three columns in the results: Rank, Name, TotalPoints. How would we do this?

The answer is hidden in the LINQ to Objects specially overloaded Select method that is available only when you use the Lambda syntax where if you specify a second parameter it will put the row index in it. Here is that same query, but with the Rank column added.

Entries.ToList()
.Select((entry, index) => new {
entry.Name,
entry.TotalPoints,
Rank = index + 1
}
)
.OrderBy (o => o.TotalPoints)

Notice how we used the ToList() to convert the query to a List first. This is critical as you will get a runtime error otherwise.

FYI, the error is: Unsupported overload used for query operator 'Select'.

Also note, the name index is not important. It is the order in the Select method that is important. The index is zero based, and I wanted to show 1 based Rankings so I added one to the index to get the Rank.

Using GroupBy with LINQ

I love LINQ, but sometimes the syntax can be a little strange. In particular, I find the group by syntax to be a little weird, but not too bad once I broke it down.

For this example, let’s assume we have a person table. The person table has a column called Score and has some other fields that don’t really matter for this example. The related table is a Gender table. It has one field of importance and that is the Name field. The two rows in this table are Male and Female. There is a foreign key in the Person table that points to the Gender table. Think of it this way. The person table is the main table, and there could be a drop down list to select the gender for the person.

With that in mind, we want to know what the total score for Males and Females. We want to do this using a group by. The results will be two columns: Gender and Score.

Here is an example of the output that we desire.

Gender	Score
Female	2013
Male	1923

Here is the lamdba based LINQ query we would need to do this.

   People
   .GroupBy (e => new {Gender = e.Gender.Name} )
   .Select (byGenderGroup =>
         new 
         {
            Gender = byGenderGroup.Key.Gender,
            Score = byGenderGroup.Sum (t => t.Score)
         }
   );

If we take this line by line we will see that this really isn’t so different from SQL that would be generated. Here is the SQL

   SELECT SUM([t0].[Score]) AS [Score], [t1].[Name] AS [Gender]
FROM [Person] AS [t0]
INNER JOIN [Gender] AS [t1] ON [t1].[ID] = [t0].[GenderID]
GROUP BY [t1].[Name]

Let’s go line by line of the LINQ code.

People is the main table we are working with

GroupBy creates an anonymous type (that way we can easily add additional columns to group by). In this case we group by the Gender.Name just like the last line of the SQL statement.

The Select lines create another anonymous type so that we can select just the columns we want to return. Notice that byGenderGroup doesn’t have a property called Gender. Since byGenderGroup doesn’t represent a person record and actually represents the grouped results, we can access any of the columns that we have grouped by in the above GroupBy line. In this case, Key collection only gives us one property, and that is Gender. The byGenderGroup does have many other methods that are available though. One example is the sum method. In general byGenderGroup has all the aggregate functions you would have in SQL.

FYI, you can also do this without using lamdba expressions, though I personally don’t like the syntax and find it confusing.

var results = from p in People group p by new {Gender = p.Gender.Name} into byGenderGroup
select new {byGenderGroup.Key.Gender, Score = byGenderGroup.Sum (t => t.Score)};

The choice is yours.

SharePoint users can no longer upload files larger than 25 MB after moving from Windows Server 2003 to 2008

I can’t take credit for this one, but it is worth a post.

Problem:

After moving SharePoint from Windows Server 2003 to Server 2008, users can no longer upload files larger than 25 MB. 

Explanation:

This is by design.  SharePoint, by default, restricts file uploads greater than 50 MB.  But IIS 7, by default, restricts file uploads greater than 25 MB.  When this threshold is reached the user gets a 404.13 error.  Of course since all users have "friendly" errors turned on, it is hard to tell why the file didn't upload.  One user even got a Google "Oops" page.

Solution:

Edit the web.config file for each virtual directory and force the threshold to be a little bigger than SharePoint's threshold.  If the threshold is hit, you want the SharePoint error to appear and not IIS's error.

   1: Replace:

   2: <system.net>

   3: <defaultProxy>

   4: <proxy autoDetect="true" />

   5: </defaultProxy>

   6: </system.net>

   7: </configuration>

   8:  

   9: With:

  10: <system.net>

  11: <defaultProxy>

  12: <proxy autoDetect="true" />

  13: </defaultProxy>

  14: </system.net>

  15: <system.webServer>

  16: <security>

  17: <requestFiltering>

  18: <requestLimits maxAllowedContentLength="52428800"/>

  19: </requestFiltering>

  20: </security>

  21: </system.webServer>

  22: </configuration>

The change is instant.  No closing of the browser or restarting of anything is needed.

KB Resource:

http://support.microsoft.com/kb/944981

Working with Dynamic Data Controls: Finding, Set Value, Get Value

Once you start working with ASP.NET, FormView, and the DynamicData controls you will soon learn that it isn’t as convenient to get and set values as you might like. You may also want to find controls not by their name, but by the column / property that they are bound to in the ADO.NET Entity Model.

I have put together some extensions to the Control class that make doing all these things much easier. All you have to do is create a class in your project called ControlExtensions. Copy and paste the code below into it. Change the namespace as needed. Then on the page you want to use it, just include the namespace. Then when you look at the methods on any control you will see the methods below. I have tested the code below with a FormView and also with DynamicControls on a User Control  that is then on a FormView. In both cases, the code works well.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.DynamicData;
using System.ComponentModel;

namespace DistiPromo.Helpers
{
    public static class ControlExtensions
    {

        /// <summary>
        /// This will find the Dynamic Data control for the specified column (name from Entity model)
        /// </summary>
        /// <param name="control">the control (such as FormView or UserControl) that directly contains the control you are looking for</param>
        /// <param name="columnName">the name of the column (from the Entity Model) that you are looking for</param>
        /// <returns>The DynamicData Control for the column that you are looking for</returns>
        public static Control FindFieldTemplate(this Control control, string columnName)
        {
            // code copied from internal method: System.Web.DynamicData.DynamicControl.GetControlIDFromColumnName
            // Since it is internal, it could change, but I needed to get to it
            return control.FindControl("__" + columnName);
        }

        public static FieldTemplateUserControl FindFieldTemplateUserControl(this Control control, string columnName)
        {
            var userControl = ((FieldTemplateUserControl)(control.FindFieldTemplate(columnName)));
            if (userControl == null) throw new Exception("Could not find FieldTemplate in Control for " + columnName);
            return userControl;
        }

        public static Control FindControlForColumn(this Control control1, string columnName)
        {
            var control = FindFieldTemplateUserControl(control1, columnName).DataControl;
            if (control == null) throw new Exception("Could not find control in Control for " + columnName);
            return control;
        }

        public static T FindControlForColumn<T>(this Control control, string columnName) where T : Control
        {
            return FindControlForColumn(control, columnName) as T;
        }

        public static object GetValueForColumn(this Control control, string columnName)
        {
            var actualControl = FindFieldTemplateUserControl(control, columnName).DataControl;
            return GetValue(control, actualControl);
        }

        public static T GetValue<T>(this Control control, string columnName)
        {
            object rawVal = GetValueForColumn(control, columnName);
            //return (T)Convert.ChangeType(rawVal, typeof(T));

            // this is a little better because it handles nullable types also
            return (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(rawVal);
        }

        public static T GetValue<T>(this Control control1, Control control)
        {
            object rawVal = GetValue(control1, control);
            //return (T)Convert.ChangeType(rawVal, typeof(T));

            // this is a little better because it handles nullable types also
            return (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(rawVal);
        }

        public static object GetValue(this Control control, Control actualControl)
        {
            if (actualControl is ITextControl)
            {
                return (actualControl as ITextControl).Text;
            }
            else if (actualControl is ICheckBoxControl)
            {
                return (actualControl as ICheckBoxControl).Checked;
            }
            else if (actualControl is ListControl)
            {
                return (actualControl as ListControl).SelectedValue;
            }
            else if (actualControl is HiddenField)
            {
                return (actualControl as HiddenField).Value;
            }
            else throw new Exception("Could not get value of unknown control type: " + actualControl.GetType().ToString());

        }

        public static void SetValue(this Control control, string columnName, object value)
        {
            Control actualControl = FindControlForColumn(control, columnName);
            SetValue(control, actualControl, value);
        }

        public static void SetValue(this Control control, Control actualControl, object value)
        {

            if (actualControl is ITextControl)
            {
                (actualControl as ITextControl).Text = Convert.ToString(value);
            }
            else if (actualControl is ICheckBoxControl)
            {
                (actualControl as ICheckBoxControl).Checked = Convert.ToBoolean(value);
            }
            else if (actualControl is ListControl)
            {
                (actualControl as ListControl).SelectedValue = Convert.ToString(value);
            }
            else if (actualControl is HiddenField)
            {
                (actualControl as HiddenField).Value = Convert.ToString(value);
            }
            else throw new Exception("Could not set value of unknown control type: " + actualControl.GetType().ToString());
        }
    }
}

Lessons in Dynamic Data, Domain Service, and DomainDataSource with ASP.NET 4

I have been working with Silverlight a lot lately and have come to love the Domain Service. I recently started a new ASP.NET based project and saw that in VS 2010 I can now create an ASP.NET Dynamic Data Domain Service Web Application. So, I tried it.

I have learned some things since I did this. Below is what I have learned.

Getting Started, Docs, etc.

• For a good starting point to learn about Dynamic Data, click here.
• Here are the best docs I can find for looking at lot of scenarios and customizations you may want to do with Dynamic Data. I recommend reading this.
• There is sample code that is referenced in the docs above. I think the code may be a bit out of date though. Anybody know where newer sample code is?
• Here is the Dynamic Data forum if all else fails.
• Once you create a new project in Visual Studio 2010 the project does run. You need to do a few things first like create your model and Domain Service. This walks you through what you need to do.

Gotchas

• If you create a custom page, you MUST have all the REQUIRED fields on the FormView in Edit mode, otherwise, the form will give your an error when you click the Update button. Click here for more details on this issue. Workarounds include setting Visible to false or creating a new FieldTemplate  or modify the existing implementation. Click here for details on how this can be done.

• I tried to create a total independent page that uses the DynamicDataSource, a form view, and my Domain Service Class and it will not work. It can’t figure out the MetaData it needs to work. The solution is to create a directory under DynamicData\CustomPages. The name should match the name of the Entity (not Entity Set). Hint, the name is the singular, not plural if version usually. To create a custom Edit page for your entity, copy DynamicData\PageTemplates\Edit.aspx (and the .aspx.cs file) to the directory you created above. Correct the class names, etc and you should be in good shape. For more details, try here.
• If you create a custom page in the DynamicData\CustomPages\<YourEntity>\Edit.ascx for example don’t use FormView1.FindControl() in the Page_Load() event. If you do it will cause the DropDownLists to not have an item selected in them. I am guessing it is just early for stuff that DynamicData is doing since if you look in the FieldTemplates a lot of stuff such as binding, etc takes place in the PageLoad event. My solution was to use the PreRender event to put my stuff there. That seems to work fine. No issues yet. :)

Here are some differences between the ASP.NET and Silverlight experience.

• The Domain Service is actually a Domain Service CLASS in ASP.NET and doesn’t use WCF or anything like that from what I can tell. In Silverlight it is actually a Domain SERVICE and actually uses WCF under the hood to communicate with your Silverlight application.
• I don’t seem to have to use .Include(“…”) queries in the Domain Service and [Include()] in the MetaData to make the ADO.NET Entity Framework pull in my related entities.

Adding a border around text to make it look like a TextBox

The scenario applies to simple Label or Literals in ASP.NET. I am currently using a DynamicControl with the Mode set to ReadOnly. This causes the generation of a Literal and thus looks like plain text in the browser. This is great in most cases.

However, sometimes I want the text to have a border around it and maybe a different background to make it look like a read only TextBox.

Thankfully there is an easy solution. Using CSS we can define a style

.DDTextWithBorder
{
        border: solid 1px #bcbcbc;   
}

We could include background color if we wanted to change that also.

To use the style, we can put a span tag around whatever we want the border around and setting the class=”DDTextWithBorder” attribute.

An example of that is:

<span class="DDTextWithBorder"><asp:DynamicControl ID="TotalPointsDynamicControl" runat="server" DataField="TotalPoints" Mode="ReadOnly"/></span>

Another way is to just set the CssClass property of the control we want to put a border around.

<asp:DynamicControl ID="TotalPointsDynamicControl" runat="server" DataField="TotalPoints" Mode="ReadOnly" CssClass="DDTextWithBorder"/>

That’s it. Quite simple, but powerful.

DomainDataSourceView.PerformCudOperation error on updating a FormView.

If you have a REQUIRED field in your ADO.NET Entity Model, you MUST have it on your Form when you use the DomainDataSource, FormView, and Domain Service Class and it must be EDIT mode if you are using the DynamicDataControl. If you leave it off the form or set the mode to ReadOnly you will get a totally useless error like this.

[EntityOperationException: An error has occurred.] Microsoft.Web.UI.WebControls.DomainDataSourceView.PerformCudOperation(ChangeSetEntry operation, Action`1 operationPerformingEvent, Action`1 operationPerformedEvent) +331 Microsoft.Web.UI.WebControls.DomainDataSourceView.UpdateObject(Object oldEntity, Object newEntity) +195 System.Web.UI.WebControls.QueryableDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +115 Microsoft.Web.UI.WebControls.DomainDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +40 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +95 System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1154 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +408 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +112 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +125 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +167 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

If you are lucky the field you set to ReadOnly will be a DataTime field or something and it will tell you that the date is not valid. If it is just a string field then you may get the useless error as shown above.

This probably won’t show up unless you try to create a Custom Page for one of your entities. The nice thing is that when you create a Custom Page you get full control over the layout of your entity. The problem is that you can create all kinds of frustrating issues like this one.

BTW, this is only an issue for datatypes that use the Text.ascx and DateTime.ascx controls. The reason is because these controls are implemented using a literal control instead of a Label, etc which has viewstate. Any datatype that has a _Edit.ascx counterpart won’t be affected and also any that have something that has viewstate as the underlying readonly implementation.

Work Arounds

Work Around – If you don’t want the user to see a field

You can always add the control to the FormView even if you don’t want the user to see it. The set the visible to false. This will allow it to work properly. All form validators will not be created which is good in for the most part.

If you are just doing an edit template then the above will work fine. However, if you are doing an insert template, then you will get errors about not being able to set the value because the value is illegal or invalid. An example of this is a DateTime can’t be set to null or an int can’t be set to null. If the field is required then non-nullable types will be used. This means you need to specify a default value.

The trick to specifying the default value is that you can’t do it in your entity’s constructor because since the FormView has the required field on the EditTemplate a value (null) will be set on your Entity after it is created (after the valid value you may have tried to default in the entities constructor. That is what is causing the error.

One solution is to use the ItemInserting event of the FormView. Here you will find a parameter that is of type FormViewInsertEventArgs. It has a collection of all the values that are being submitted.

For each of the items you added to the FormView and hide, you will need to set the default value they should have using the Values collection. The key is the name of Entity Property you bound to in the FormView. The exception to that is if you re binding to a Navigation property such as with a DropDownList. In this case the foreign key is actually what you will find in the Values collection, not the Navigation property name. The value is foreign key, not the related entity.

If you don’t want to think that hard, you can always just set the values of the DynamicControls you want to default using what ever method gets called when you click your insert button. In my case, I am going with the same LinkButtons that are used in the default insert template for Dynamic Data. This means that FormView1_ItemCommand is a good place to set the values of your DynamicControls. This is a bit easier because you don’t have to worry about DropDownLists in the same way. However, since you are dealing with different controls that is more thought also, so it is really up to you on which approach you want to take. The bottom line is that the value has to be there when the FormView passes it on to the DomainDataSource.

FYI, I have made working with DynamicControls must easier. It makes setting and getting values on forms that have DynamicControls on them trivial. Here is an example of what I am talking about.

FormView1.SetValue("DateCreatedUtc", DateTime.UtcNow);

Here is the link to the source code. I implemented it as an extension of the Control class.

Work Around – If you want it on the Edit mode of the FormView, but show as readonly.

Option 1

If you need the field on the FormView, but want it to be readonly when the FormView is in Edit mode, there is another solution.

Copy DyanmicData\FieldTemplates\Text_Edit.ascx (and the .ascx.cs file) to DyanmicData\FieldTemplates\TextAppearReadOnly_Edit.ascx (and .ascx.cs file). What comes before the underscore is not important, but what the name must end is _Edit.ascx and _Edit.ascx.cs.

Remove the validator controls as they are not needed. Add a literal control and bind it to the same value as the TextBox. Once you have done that, your .ascx file will look something like this:

<asp:Literal runat="server" ID="Literal1" Text="<%# FieldValueEditString %>" />
<asp:TextBox ID="TextBox1" runat="server" Text='<%# FieldValueEditString %>' ReadOnly="true" Visible="false" />

Now open the .ascx.cs file. Remove all items that aren’t needed or don’t compile. You class will look something like this:

public partial class TextAppearReadOnly_EditField : System.Web.DynamicData.FieldTemplateUserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {
        TextBox1.ToolTip = Column.Description;
    }

    protected override void ExtractValues(IOrderedDictionary dictionary)
    {
        dictionary[Column.Name] = ConvertEditedValue(TextBox1.Text);
    }

    public override Control DataControl
    {
        get
        {
            return TextBox1;
        }
    }

}

Now, where ever you want a read only field on do something like this:

<asp:DynamicControl ID="DateCreatedDynamicControl" runat="server" DataField="DateCreated" Mode="Edit" UIHint="TextAppearReadOnly" />

The part to pay attention to is that the Mode is set to Edit and the UIHint specifies to use our control we created.

Option 2

I actually prefer this method as the semantics of option 1 don’t seem quite right. In this option, we will actually modify the default controls to implement what I call a bug fix. :)

1. Open DynamicData\FieldTemplates\DateTime.ascx
2. Change the line:
   
   <asp:Literal runat="server" ID="Literal1" Text="<%# FieldValueString %>" />
   
   to
   
   <asp:Label runat="server" ID="Label1" Text="<%# FieldValueString %>" />
   
   Notice the only change I made is changing the type from Literal to Label and updated the ID to reflect the change also.
3. Open DynamicData\FieldTemplates\DateTime.ascx.cs
4. Add the following method:
   protected override void ExtractValues(IOrderedDictionary dictionary) {
               dictionary[Column.Name] = ConvertEditedValue(Label1.Text);
   }
   
5. Add the DataControl property (or modify it if it already exists) as shown below
   
   public override Control DataControl { get { return Label1; } }

Do the Same for Text.ascx (and Text.ascx.cs).

Using this fix, you don’t have to change any of the properties on the DyanmicControl and the semantics are right. Here is an example of what you may have.

<asp:DynamicControl ID="DateCreatedDynamicControl" runat="server" DataField="DateCreated" Mode="ReadOnly" />

Notice that the Mode is ReadOnly which makes sense to me. This means that you can use the DynamicControl exactly the same regardless of the datatype and regardless of whether it is a required field or not.

Adding a linked server in MS SQL Server

A linked server allows you to connect and query against a database on another MS SQL Server instance even if it is on another machine. Once you have created a linked server you can use the tables in the linked server just like you would use a local table (in most cases).

Let’s assume you have to instances of MS SQL Server on two different machine. On the first instance (called it SQL1) you have a database called MyDB1. On the second instance (call it SQL2) you have a database called MyDB2. Now we want to run a query from MyDB1 on MyDB2.

In general here is what we need to do

1. Create a linked server
2. Specify the credentials that the link will use

Specifically here is what we need to do.

1. Open SQL Management Studio and open a query window for MyDB1.
2. Create the linked server. I prefer the SQL statement way of doing this as I think the UI in MS SQL Management Studio under Server Object | Linked Server is confusing to use since it is made to work for all kinds of server besides MS SQL server. 
   
   Customize (change the items in red and green) the SQL statement below to point to your stuff and then execute it in the query window. Please note that the item in red is what will show up under the Server Objects | Linked Servers in SQL Server Management Studio. The item in green is the name of the database you want to get access to on the remote server.
   
   EXEC master.dbo.sp_addlinkedserver
   @server = N'MyDB2LinkedServer',
   @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI', @datasrc=N'SQL2',
   @catalog=N'MyDB2'
   
3. I can never seem to get Windows credentials to work for linked servers, so I usually go with a named SQL Server user. The example below uses SQL Server user named DB2User and has a password of db2user. Please use a better password in a real environment.
   
   Customize (change the items in red and green) and execute the sql statement in the same query window. Please note the item in red MUST match what you used in the first statement (above). The items in green must be the username and password of the user / login on the remote server you are trying to access and must have access to the database you are trying to access on that same remote server.
   
   EXEC sp_addlinkedsrvlogin 'MyDB2LinkedServer', 'false', NULL, 'DB2User', 'db2user'

 

Now that you have the link established, it should show up in SQL Server Management Studio under Server Objects | Linked Servers. If it doesn’t try right-clicking the Linked Servers node and choose Refresh.

Now that you have a link it is time to give it a try. We’ll do this by selecting from table on the remote server from the local server. Using the scenario discussed earlier, follow the steps below to test your linked server.

1. In the same query window or a new one. For a new one, open SQL Management Studio and open a query window for MyDB1.
2. Customize the statement below for your stuff and execute it.
   
   select count(1) from MyDB2LinkedServer.MyDB2.dbo.MyTable1
   
   Here we are selecting from a table called MyTable1 on the MyDB2 database on SQL2 all the while we are still connected to MyDB1 on SQL1. If you tables are not in the dbo schema you will need to change dbo to be the name of htat schema. Typically you can tell just by looking in SQL Server Management Studio and browsing the tabes. If there is just a table name, then it is almost certainly dbo. If it same xxx.MyTable1 then the schema is called xxx and the above statement will need to have have dbo replaced with xxx.
   
   Pretty cool. You can then join tables, update and update rows, etc just as you would any other tables in your local database.