I consider myself as a quite experienced user of Nhibernate, that is why I decided to check what is going on on the other side of barricade and started playing with Entity Framework. I followed the steps of some tutorial and I stuck at the very beginning of it. I was about to generate model classes using “Entity Data Model Wizard” but it turned out that the wizard could not find my SQL server (despite the fact that server itself was up and running).
I remember having similar issue a while back, but at that time I wasn’t able to solve it (I ended up creating connections string manually). This time I wanted to give this issue a closer look. After a bit of digging I found the problem – the SQL server wasn’t broadcasting information about itself. In order to fix it and make the server visible to other computers and applications it is necessary to start SQL Server Browser service. To do that go to Start -> Run and type Services.msc
Then in newly opened window find SQL Server Browser service
Double click it and set up startup type to Automatic. You can also click Start button to make sure that service starts immediately
From now on SQL servers should be visible in “Entity Data Model Wizard”
T-SQL – syntactic sugar for updating current row in cursor
I am not a big fan of cursors, however for time to time I have to write one. In my case cursor usually is responsible for some complicated updates. Until yesterday I’ve been writing my update statements within the cursor the standard way
|
1 2 3 4 5 |
-- cursor code omitted for brevity -- some complicated update UPDATE Person.Person set FirstName = 'some logic goes here' where Person.BusinessEntityID = @Id where @Id variable is set by cursor. -- cursor code omitted for brevity |
There is nothing wrong with that, however we can do the same thing using specialized syntax – current of keyword(s)
|
1 |
UPDATE Person.Person set FirstName = 'some logic goes here' where current of personCursor |
The entire cursor statement then looks that way
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE personCursor CURSOR FOR SELECT top 1000 * FROM Person.Person OPEN personCursor FETCH NEXT FROM personCursor WHILE @@FETCH_STATUS = 0 BEGIN -- some complicated update goes here UPDATE Person.Person set FirstName = 'some logic goes here' where current of personCursor FETCH NEXT FROM personCursor END CLOSE personCursor DEALLOCATE personCursor |
T-SQL – executing query multiple times
For time to time during my everyday work I have to do some SQL query optimization. The simplest way of measuring whether my changes improved the performance or not, is simply measure the execution time of given query (I know that this is not the best way of doing that, but let’s leave aside execution plans for now). However measuring time of single executin is not very meaningfull. Thats why I like execute query multiple times by using this simple trick
|
1 |
GO 100 |
and then calculate an average time of execution by dividing the total time by execution count. As You can see, all You have to do is to specify execution count after the GO statment. Despite the fact that Management Studio indicates that there is an error in the SQL, this is perfectly valid code. Of course You should hide execution results in order to measure only the time of execution, not the time of rendering data. To do that, right click on query window and choose “Query Options…”
then select “Results” node and check “Discard results after execution”
INotifyPropertyChange – notifying about change of all properties at once
Today, working on a new feature for my pet project, I realized that I have to notify the view, that all properties in view model have changed. The most obvious way to achieve that would, of course be to rise PropertyChange event a bunch of times.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
protected virtual void OnPropertyChanged(Expression<Func<object>> propertyExpression) { //.. //implementation //.. } protected virtual void OnPropertyChanged(string propertyName) { //.. //implementation //.. } public void Refresh() { OnPropertyChanged(() => FirstProperty); OnPropertyChanged(() => SecondProperty); OnPropertyChanged(() => ThirdProperty); //and so on ... } |
This is good solution for one time usage, however I was interested in something more general, something which could be extracted to base view model. Fortunately, it turns out that there is a simple trick to do that. All You have to do is use an empty string or null as a property name. So in my case this comes down to this one-liner
|
1 2 3 4 |
public void Refresh() { OnPropertyChanged(string.Empty); } |
NHibernate – generating WHERE IN … OR … queries
Let’s assume that we have a simple table GL_Task which looks like this
I was asked to rewrite simple SQL query
|
1 |
SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' OR Id IN (3,4 /*more id's to come*/) |
using NHibernate’s QueryOver API. As simple as it may seem, solution for this particular problem is not straightforward. My first (not so clever) attempt was simply combining WhereRestrictionOn and Where clause
|
1 2 3 4 |
var result = session.QueryOver<Task>().Where(val=> val.Name == "First task") .WhereRestrictionOn(val=> val.Id) .IsIn(new []{3,4}) .List(); |
Of course this query returns wrong data because of the fact, that NHiberante by default “joins” queries using AND operator. So above query will result in generating something like that
|
1 |
SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' AND Id IN (3,4 /*more id's to come*/) |
After a little digging I managed to write appropriate query
|
1 2 3 4 5 6 7 |
var orRestriction = Restrictions.Or( Restrictions.Where<Task>(val => val.Name == "First task"), Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 })); var result = session.QueryOver<Task>() .Where(orRestriction) .List(); |
Although this code generates correct SQL, it is hard to use it in case of many OR statements. For example, adding one more OR force us to write
|
1 2 3 4 5 6 7 8 9 10 11 |
var orRestriction = Restrictions.Or( Restrictions.Or( Restrictions.Where<Task>(val => val.Name == "First task"), Restrictions.On<Task>(val => val.Id).IsIn(new[] {3, 4}) ), Restrictions.On<Task>(val => val.Name).IsIn(new[] {"FirstTask", "SecondTask"}) ); var result = session.QueryOver<Task>() .Where(orRestriction) .List(); |
In my opinion this is a little bit hard to read, so I continued my searching to find more elegant solution. My third attempt was based on Restrictions.Disjunction
|
1 2 3 4 5 6 7 |
var disjunctinos = Restrictions.Disjunction() .Add(Restrictions.Where<Task>(val => val.Name == "First task")) .Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 })); var result = session.QueryOver<Task>() .Where(disjunctinos) .List(); |
This was almost perfect solution. Adding new OR statements do not require us to do function nesting
|
1 2 3 4 5 6 7 8 |
var disjunctinos = Restrictions.Disjunction() .Add(Restrictions.Where<Task>(val => val.Name == "First task")) .Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 })) .Add(Restrictions.On<Task>(val => val.Name).IsIn(new[] { "FirstTask", "SecondTask" })); var result = session.QueryOver<Task>() .Where(disjunctinos) .List(); |
However the ultimate solution is to just add NHibernate.Criterion namespace and use IsIn extension method. This operation simplify our query into single lambda expression
|
1 2 3 |
var result = session.QueryOver<Task>() .Where(val => val.Name == "First task" || val.Id.IsIn(new[] { 3,4 })) .List(); |
Source code for this post can be found here