Skip to content
Archive of posts filed under the Linq category.

SQL CE in Mango–Updating the Schema

Posted by Sergey Barskiy on 10 July 2011, 5:02 pm

In my previous post I talked about general usage of SQL CE database in Mango.  In this example I am going to talk about keeping the database schema updated.

For example, I want to add a column to Person table from previous post.  I would like to add Notes column with the following definition:

        [Column(DbType = "NVarChar(300) NULL")]

        public string Notes { get; set; }

 

Now I am going to use DatabaseSchemaUpdater class to accomplish this.  First of all, I have to add using statement to my program in order to invoke extension method on my data context:

using Microsoft.Phone.Data.Linq;

 

Now, I am going to add some code after the database exists check and add the column as follows:

                DatabaseSchemaUpdater db = App.CurrentApp.DB.CreateDatabaseSchemaUpdater();

                if (db.DatabaseSchemaVersion == 0)

                {

                    db.AddColumn<Person>("Notes");

                    db.DatabaseSchemaVersion = 1;

                    db.Execute();

                }

 

Here is what I am doing here.  I am checking for DB version, which I am going to assume responsibility for updating from now on.  I am adding notes column called Notes to Person table.  Then I am bumping version number for subsequent updates.  Lastly, I am firing Execute method to commit my changes.  Pretty easy all-and-all.  I only wish that Entity Framework code first had similar functionality.

Post to Twitter

Filed under Data, Linq, Windows Phone 7.
Comment

Using sorting functions with Linq for Objects

Posted by Sergey Barskiy on 24 January 2009, 6:25 pm

If you have written Linq queries, you are familiar with OrderBy clause.  Here is a quick example:

from one in selected
select one).OrderBy(one => one.Name)
 

If you only need to sort by single field, or even many fields, you are OK.  Here is an example:

from one in selected select one).OrderBy(one => one.Name).ThenBy(one=>one.Title)

But what if you would like to sort by something more complicated, such as sum of Salary + Bonus in descending order, or even something more complex that that.  Well, you can use a delegate that Linq provides for sorting:

return (from one in selected
select one).OrderBy(one => SortFunction(one))
     //descending sort     return (-returnValue);
private double SortFunction(MyObject member)
{
     double returnValue = member.Salary + member.Bonus;
}

 

The only catch your function must return the type that implements IComparable,which all primitive types do.  You can actually even use a custom return types and use an additional OrderBy overload that accepts another parameter – custom comparer.

You can also do something very similar in Where clause that also accepts delegates.  Pretty impressive if you ask me.

Post to Twitter

Filed under Linq.
Comment