SQLite: How to Enforce Foreign Key Constraints

Enabling SQLite Foreign Keys:

SQLite lets create foreign keys but will not enforce them upon insertion or deletion. I spent sometime playing with this and it turns out that there are two ways to make SQLite enforce the foreign key constraint:

1) And the this is the easiest way. In the connection string you can specify that foreign keys should be enforce like so (tested using System.Data.SQLite ADO.NET driver):

<add name="ConnectionName" connectionString="Data Source=dbname.db;Version=3;New=True;Foreign Keys=True;" providerName="System.Data.SQLite"/>

2) For every connection you can turn on the foreign_keys pragama using the command
PRAGMA foreign_keys = ON; as documented here http://www.sqlite.org/foreignkeys.html#fk_enable

So with the second approach one can do something similar to the following pseudocode:

SQLiteConnection cn = new SQLiteConnection(...);
SQLiteCommend cmd = new SQLiteCommand(cn);
cn.Open();
cmd.CommandText = "PRAGMA foreign_keys = ON;"
cmd.ExecuteNonQuery(); /* so far we ensured that foreign keys constraints will be enforced */
cmd.CommandText = /* some SQL code here */
cmd.Execute ...

I prefer approach 1).

Passing Func When you Meant Expression

Queries are Very Slow

While working on a proof of concept project, I noticed my queries took longer than expected to run. The repository class uses an Entity Framework (Code First) DbContext. The function in the repository class in question had a signature that looked like:

public IEnumerable<entity> Find(Func<Entity, bool> whereExpression,int take,int skip){
return this.dbContext.Entities.Where(whereExpression).Skip(skip).Take(take);
}

The idea is I will let the client of the repository pass any expression that will return a boolean value to filter the entities collection.

I noticed the generated SQL was something like

select column1, .... columnn from dbo.table where <some_condition>

I did expect the output query to specify a limit to the query at the end (this was PostgreSQL). So I expected:

select column1, .... columnn from dbo.table where <some_condition> limit n;

I ran the same code against Microsoft SQL Server 2012 and the generated SQL had not top keyword to limit the number of returned rows.

What was Happening?

Basically the where operation and the take calls were not happening on the database server, they were being executed on the client-side, the C# console app in my case. The entire table (thousands of rows) of Entities was brought to the client and loaded in memory after the filtering and limiting took place. Very expensive and that explained why the query was much slower than I expected. 

Why was the Entire Table Loaded to Memory?

I just needed the top n rows so why the entire table loaded in memory? After browsing the documentation I came to the following understanding and conclusion. In LINQ if you enumerate a query it will cause the runtime to execute it to get the result. Enumerating a query is typically done via calls to function such as ToList(), or via a foreach expression. So which call in my two-line function was causing the Entities property to be executed and enumerated. Entities is of type DbSet which implements both IEnumerable and IQueryable and both interfaces have a Where method http://msdn.microsoft.com/en-us/library/gg696460(v=vs.113).aspx. Which implementation of where to call is determined by the type of the argument. Notice that IEnumerable's where looks like 

Where<TSource>(Func<TSource, Boolean>)

Whereas IQueryable's where looks like 

Where<TSource>(Expression<Func<TSource, Boolean>>)

I noticed that when I called my Find function with Func<T,bool> as the type of the whereExpression variable, the Entities set was enumerated and loaded to memory as soon as the Where method is called. When I however pass an Expression (and not just a delegate) the following happened:

• I got my limit statement (or top statement in T-SQL) in the generated SQL code
• Therefore not all instances of Entity was loaded in memory
• The query was faster as a result.

So the new signature of my Find function is 

public IEnumerable<entity> Find(Expression<func>Entity,bool>> whereExpression,int take,int skip)

Summary

There is a difference between passing a delegate Func or an Expression. Here is a good answer to the difference http://stackoverflow.com/a/3123181. Applying either a delegate or an expression has an effect on LINQ to Entities queries.

 

Connect to Wireless Network from Command Prompt or Terminal

Why?

If you're Windows o Mac OS X is not configured to connect automatically to your wireless network, it can be a bit tedious to use the GUI in either OS to connect to your WiFi. Here are two commands to connect to a a wife:

Windows:

The netsh command and its subcommands is a powerful way to interact with windows networking subsystem. Follow this http://technet.microsoft.com/en-us/library/cc755301(v=ws.10).aspx for more on this command. Here is how to connect to a WiFi:

The command:

netsh wlan connect name=your_wireless_network_profile_name ssid=your_wireless_network_ssid

If you are not sure about the network profile (normally it's the same as the ssid) you can use netsh command to list all profile:

netsh wlan show profiles

If you want to see the available networks then use:

netsh wlan show networks

Netsh is a great command and can do a lot more.

Mac OS X:

The networksetup command is a powerful command in the Mac OS X environment to deal with network setup. 

To connect to a wireless network for example:

networksetup -setairportnetwork hardware_port your_wireless_network_ssid your_password

A typical value for hardware_port (wireless) is en1 (ethernet interface # 1).

There is more to this command for sure.

Array Traversal i++ vs i--

Array Bounds-Check Removal

One of the optimization done in .NET CLR is removing bounds-check when accessing arrays. It's done in certain cases and this excellent blog post discusses these cases (http://blogs.msdn.com/b/clrcodegeneration/archive/2009/08/13/array-bounds-check-elimination-in-the-clr.aspx)

Something that I found really interesting is that it matters as far as bounds-check elimination is concerned whether arrays traversed in an increasing or decreasing order.

for(var i=0;i<someArray.Length;i++){ //array access using i} vs. for(var i=someArray.Length-1;i>=0;i--) { //array access using i }

 So in accessing the array within with the first loop, the bound check is eliminated but in the in the second case (decreasing; i--). The mentioned posts recommend traversing the array in ascending order whenever possible.

Another useful advice from the post is that using the Length property in the condition statement of a for loop is better than using a local variable (unless the local variable is intended to access a subset of the array of course). Interesting stuff.

 

 

 

 

Generate SQL to Search All Columns in a Table

If you are searching for a given value in a table with numerous columns not knowing which column could match the value, you'll find it tedious to write as many OR clauses as columns in your table.

Here is a simple T-SQL script to generate SQL code with all the column names and the OR clauses.

 declare @sql nvarchar(max);
 select @sql = 'select * from MY_TABLE_NAME where ';
 select @sql = @sql + c.name + ' = ''value'' or '
 from sys.tables t 
  inner join sys.columns c on t.object_id = c.object_id 
  and c.system_type_id in(231,167) --this restricts target columns to just nvarchar,varchar types
  and t.name = 'MY_TABLE_NAME';
  
 select @sql = left(@sql,len(@sql)-3)+';'; -- remove the last or
 print @sql;

MY_TABLE_NAME has to be changed to your specific table of course. This script can be improved to add conditional casting. When the column type (system_type_id) is string then no casting is needed but when integer then convert to varchar and when the type is datetime for example then use convert(varhcar(10),101) for example.

C#/VB.NET Optional Parameters and the MissingMethodException Exception

Optional Parameters 

Optional parameters is a useful feature in C# and VB.NET languages. Adding an optional parameter to a method can sometimes make re-factoring a bit easier and less invasive. There is however a catch one should know. I ran into this situation a few days ago and here is my attempt to describe it:

Initially

Suppose you have a library, let's call it OptionsLib. In this class library lets say we have class called Common. In this Common class there is a method call DoSomeWork whose signature looks initially like

public void DoSomeWork(){
   DoStepOne();
   DoStepTwo();
   DoSomeOtherStep();

}

Now we have a consumer of this OptionsLib library and let's call it OptionsClient and it exists in its own assembly. So we have two assemblies: OptionsLib v1.0.0.0 and OptionsClient v1.0.0.0.

New Version: Minor Change

You've decided to make a minor change to your OptionsLib.Common class. Namely you wanted to add some conditional logic to the DoSomeWork method. So the new method would look something like:

public void DoSomeWork(bool someCondition = true){
   DoStepOne();
   DoStepTwo();
   if(someCondition){
       DoSomeOtherStep();
   }
}

There are probably other libraries that depend on the OptionsLib library so making the new parameter optional alleviates you from having to change the way the method called everywhere. The existing behavior is retained by making the new parameter optional and giving it a default value of true. So now re-compiling the whole solution will yield no errors as a result of the new optional parameter. The compiler will realize the new parameter is optional and will take care of passing the default value for you everywhere the method DoSomeWork is called.

The Issue: System.MissingMethodException

Let's say you found a bug in some of the libraries that use OptionsLib library or a change is made to one of its clients and you wanted to push that change to existing installations. In our example, suppose we may have fixed some bug or added a feature to the OptionsClient.dll assembly. So now OptionsClient.dll is compiled against the new version of OptionsLib, the version with the optional parameter change. In the existing installations however, the OptionsLib does not have the optional parameter we added.

For the most part placing the new OptionsClient.dll assembly in existing installations will be OK. It will find its referenced assemblies in the existing installations and everything seems to work fine. Until it starts calling the method DoSomeWork which in the new version has an optional parameter but not in the existing version. So in reality the new OptionsClient call of DoSomeWork looks like:

...DoSomeWork(true); // my understanding is that the compiler passes true for you                                               // because it's optional and the default is true

When the above call is made the runtime will complain because there is no method named DoSomeWork in v1.0.0.0 of OptionsLib that takes a boolean; there is one with no parameters at all but it's not what was called. The runtime then throws the System.MissingMethodException exception. So you'll realize that you may need to also recompile and ship the OptionsLib v1.0.0.1 the one with optional parameter

I Learned

Optional parameters are useful but sometimes adding them to a library can break dependents assemblies on this the changed library when the dependent assemblies are deployed alone without the changed library. I think I'd prefer method overloading when re-factoring.

http://msdn.microsoft.com/en-us/library/dd264739.aspx optional parameters
http://msdn.microsoft.com/en-us/library/zk1a255s(v=vs.80).aspx MissingMethodException

 

Entity Framework Code First Does Not Create Database

Where is the Database?

I spent more time than I should have trying to troubleshoot why my database not being created. My DbContext-based class looked like:

    public class TakeNoteContext : DbContext
    {
        public TakeNoteContext()
            : base("name=TakeNote")
        {
            
        }
        public DbSet Posts ;
        public DbSet Users;
        public DbSet PostTypes;
        public DbSet SourceTypes;

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //base.OnModelCreating(modelBuilder);
            //an alternative to using attributes
            modelBuilder.Configurations.Add(new PostConfiguration());
        }
    }

Everything looked right to me, initially. And later I noticed that I was using fields and properties for my DbSet data members.

The correct way:

    public class TakeNoteContext : DbContext
    {
        public TakeNoteContext()
            : base("name=TakeNote")
        {
            
        }
        public DbSet Posts { get; set; }
        public DbSet Users { get; set; }
        public DbSet PostTypes { get; set; }
        public DbSet SourceTypes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //base.OnModelCreating(modelBuilder);
            //an alternative to using attributes
            modelBuilder.Configurations.Add(new PostConfiguration());
        }
    }

The interesting thing is that the database can be created if I call ctx.Database.CreateIfNotExists(); directly even if I used fields. There might other reasons why your database is not created. My sure your connection is correct, for example.