System.Data.SQLite

 Hi Robert,

 I use a set of linq queries (linq to entities RTM) to get data from a database containing music information (http://www.luminescence-software.org/download/MetatOGGer-270808.exe) :

// exact search, case sensitive

from album in entities.AlbumJeu.Include("Artist")
from track in entities.TrackJeu
where album.Name == qAlbum && album.Artist.Name == qArtist && track.Title == qTitle
select album).Distinct().Take(500);

// exact search, case insensitive

from album in entities.AlbumJeu.Include("Artist")
from track in entities.TrackJeu
where album.Name.ToLower() == qAlbum.ToLower() && album.Artist.Name.ToLower() == qArtist.ToLower() && track.Title.ToLower() == qTitle.ToLower()
select album).Distinct().Take(500);

// case sensitive

from album in entities.AlbumJeu.Include("Artist")
from track in entities.TrackJeu
where album.Name.Contains(qAlbum) && album.Artist.Name.Contains(qArtist) && track.Title.Contains(qTitle)
select album).Distinct().Take(500);

// case insensitive

from album in entities.AlbumJeu.Include("Artist")
from track in entities.TrackJeu
where album.Name.ToLower().Contains(qAlbum.ToLower()) && album.Artist.Name.ToLower().Contains(qArtist.ToLower()) && track.Title.ToLower().Contains(qTitle.ToLower())
select album).Distinct().Take(500);

1. With the first query, the Include statement doesn't work (artist data is not loaded, I must invoke ArtistReference.Load()).

2. My queries are not optimized : case insensitive search don't use indexes, for example. How can I optimize them ?

3. Is there a way to trace the SQL code generated by the linq to entities provider ?

 Thanks for your help.

Cyber Sinh

I'm afraid my knowledge of LINQ is a bit limited.  One thing I did to my tables was to declare the text columns with COLLATE NOCASE so I could use the normal "==" operators without resorting to ToLower(), which ruins your shot at using indexes.

As for tracing the SQL code ... I've been using the sample code from http://code.msdn.microsoft.com/EFQuerySamples to see how they get the SQL for a generated statement and so forth.  Quite interesting.

I'm also using that for unit testing.  I created a SQLite northwind database and generated the csdl file and so forth, modified the config file, and so far its been quite useful for finding bugs.

Robert Simpson:

I'm afraid my knowledge of LINQ is a bit limited.  One thing I did to my tables was to declare the text columns with COLLATE NOCASE so I could use the normal "==" operators without resorting to ToLower(), which ruins your shot at using indexes.

 If I define text columns with COLLATE NOCASE, I loose the ability to do case sensitive search. I must do case sensitive AND case insensitive search...

Robert Simpson:

As for tracing the SQL code ... I've been using the sample code from http://code.msdn.microsoft.com/EFQuerySamples to see how they get the SQL for a generated statement and so forth.  Quite interesting.

I will look that.

Robert Simpson:

I'm also using that for unit testing.  I created a SQLite northwind database and generated the csdl file and so forth, modified the config file, and so far its been quite useful for finding bugs.

Do you want a sample program that reproduce the bug? If yes, please give me your mail.

 Thanks very much for your library. You have done a very good work !!

Cyber Sinh

The bottom line is that ToLower() evaluates to a built-in function that eliminates the possibility of using the index on the column.

Either way ... one search is going to use the index and one not.  If you really have to have both sensitive and insensitive, then why not have two columns in the database -- one for an all lowercase title and one with the normal title?

Robert

 For the bug, where can I send you a repro?

Sorry I'm unclear ... what is the bug exactly?

 var albums =
               (from album in ent.AlbumJeu.Include("Artist")
                from track in ent.TrackJeu
                where album.Name == qAlbum && album.Artist.Name == qArtist && track.Title == qTitle
                select album).Distinct().Take(500);

In this query, I specify an include clause to load automatically all Artist entities associated with the returned Album entities (1 --> n).

But, Artist entities are not loaded... I must load them manually with:

if (!ArtistReference.IsLoaded)
               ArtistReference.Load();

Does it work properly in another engine like Sql Server Express?

 I don't use SQL Server.

It seems that the ArtistReference.Load(); method doesn't always retrieve data as requested.

The functionality you're describing is behavior outside the scope of the actual SQLite LINQ provider and more into the actual LINQ/Entity Framework itself. 

SQLite's Entity Framework provider doesn't do the loading or unloading, or any state management or any of that stuff.  It just takes a query command tree and generates the SQLite-compatible SQL statement for it.

For a situation like this, the only way to determine if what is happening is a bug or not, is to attempt to do the same steps in a Microsoft database engine like Sql Server Express.  If Sql Server Express gives you the same result as SQLite, then I know its not a bug and I don't have to spend hours chasing after it.

So if you can't install Sql Express or Sql Server CE and translate your database over and try it out there -- then send it to me and I'll see what I can do.  I'll need the db, and your LINQ queries.

robert at blackcastlesoft dot com

 It's a bug in SQLite. Robert posted a ticket to the sqlite.org website.

If you want to keep track of it, you’ll find the bug report here: