Toolroom Tech Blog

Devlopers Digest

Linq: Order by Null last

How to order null elements to the bottom with Linq

Sometimes you might to order values, where Null values should go to the bottom of the result. To achieve this, just use the following:

myObjects.OrderBy(_ => _.Value == null).ThenBy(_ => _.Value);

 

T-SQL statement for Entity Framework query

A very useful snippet that creates the T-SQL query for a linq statement.

Snippet

This very useful snippet below creates the T-SQL query from a linq statement.

/// <summary>
/// Returns the T-SQL string for a given query
/// </summary>
/// <param name="queryable">Source ObjectQuery object to get the SQL for</param>
/// <returns>T-SQL statement for the input query object</returns>
public static string GetQuery(this IQueryable queryable)
{
	if(queryable == null || !(queryable is ObjectQuery))
		throw new ArgumentException("Argument is null or no ObjectQuery.", "queryable");
	 
	return ((ObjectQuery)queryable).ToTraceString();
}

Linq to Sql: Row not found or changed

Finding a way back from LINQ to SQL cuncurrency hell.

Yesterday I was trapped in concurrency hell and had no idea why.

Background

The Windows Phone App I am currently working on, synchronizes data between a local database and a REST service. When the app receives an update for a row, I select the row to be updated from the local database via LinqToSql, update its values with the data from the service and save it.

The issue

As you can see in the snippet below, I select a record, update it ... and a few lines later the update crashes with a lovely System.Data.Linq.ChangeConflictException saying "Row not found or changed". How is that possible? I'm sure that nobody modifies or deletes the data in the meantime.

private void updatePerson(Person person)
{
	var localPerson = DataStore.Data.Persons.Where(p => p.Id == person.Id);
	if(localPerson == null)
	{
		//person is new, so create it
		DataStore.Data.Persons.InsertOnSubmit(person);
	}
	else
	{
		//update existing person
		localPerson.Name = person.Name;
		localPerson.CustomerId = person.CustomerId;
		localPerson.DepartmentId = person.DepartmentId;
		localPerson.IsActive = person.IsActive;
	}
	
	DataStore.Data.SubmitChanges(); //crash boom bang --> Row not found or changed.
}

Aha! LINQ to SQL uses Optimistic Concurrency

Hahaaa, that's it! When LINQ to SQL selects a record, it does not lock any data. When it is updating a record, it compares all columns with their original values.

So what does this mean? The update query that I expected from LINQ to SQL was:

UPDATE Persons
SET Name = 'Mr. Buggy',
	CustomerId = null,
	DepartmentId = 6,
	IsActive = true
WHERE
	Id = 9999

But the real world is different; due to optimistic concurrency it compared all columns:

UPDATE Persons
SET Name = 'Mr. Buggy',
	CustomerId = null,
	DepartmentId = 6,
	IsActive = true
WHERE
	Id = 9999 AND
	Name = 'Mr. Buggy' AND
	CustomerId = 1234 AND
	DepartmentId = 1 AND
	IsActive = true

This still does neither explain nor solve my issue, since my original values did not change in the meantime. But with the knowledge of Optimistic Concurrency and a few more minutes of debugging, I found (my) fault: The database field for CustomerId is not nullable ... but the REST Service returned null.

I fixed this and everyone (that's me) is happy!

Properly adding UIElements from code

Although it's not the way how you should design your WPF applications, you may try to add UIElements at runtime from code behind. Therefore you should introduce the recently added elements into the XAML namescope. Otherwise you will not be able to find the added item  again and you may run in troubles with animation storyboards, since they use runtime name lookups with their key property "TargetName".

In the sample below, a row with a label and a textbox is added to a grid at runtime:

private void AddParam(int rowId, string labelText, string displayValue)
{
	grid.RowDefinitions.Add(new RowDefinition { Height = GridLength.Auto });

	var label = new Label { Content = labelText };
	label.SetValue(Grid.RowProperty, rowId);
	label.SetValue(Grid.ColumnProperty, 0);
	grid.Children.Add(labelO);

	var textboxName = string.Format("dauerPosParam{0}__O", rowId);
	var textbox = new TextBox { Text = displayValue, Name = textboxName };
	textbox.SetValue(Grid.RowProperty, rowId);
	textbox.SetValue(Grid.ColumnProperty, 1);
	grid.Children.Add(textbox);
	if(FindName(textboxName) == null)
		RegisterName(textboxName, textbox);
}

Get further Information at FrameworkElement.RegisterName or NameScope.RegisterName.