Useless connection opening/closing in Linq query

[gwihen]

-----------------------------------------------------
Here is the code:

/*
"Entities" is a DbContext Entity class (using EntityFramework.4.3.1).
SMS is an entity with members ID (Entity Key), TEL, DATE, BODY, etc.
*/

First version:
using (Entities ctxt = new Entities())
{
foreach (FileInfo fi in arr)
{
SMS rec = new SMS();
rec.FromFile(fi);
var q = from r in ctxt.SMSs
where r.TEL == rec.TEL && r.DATE == rec.DATE
select r;
int N = q.Count();
if (N == 0)
ctxt.SMSs.Add(rec);
}
try
{
ctxt.SaveChanges();
}
catch (Exception ex)
{
// ...
}
}

Second version:
using (Entities ctxt = new Entities())
{
// Open connection for RecCountOf() calls
sqLiteCommand1.Connection = (Devart.Data.SQLite.SQLiteConnection) ctxt.Database.Connection;
sqLiteCommand1.Connection.Open();
foreach (FileInfo fi in arr)
{
SMS rec = new SMS();
rec.FromFile(fi);
if (RecCountOf(rec) == 0)
ctxt.SMSs.Add(rec);
}
try
{
ctxt.SaveChanges();
}
catch (Exception ex)
{
// ...
}
}


private int RecCountOf(SMS rec)
{
// sqLiteCommand1.CommandText is "SELECT COUNT(*) FROM SMS WHERE TEL = :TEL AND DATE = :DATE"
sqLiteCommand1.Parameters["TEL"].Value = rec.TEL;
sqLiteCommand1.Parameters["DATE"].Value = rec.DATE;
SQLiteDataReader sqReader = sqLiteCommand1.ExecuteReader();
int N = 0;
try
{
sqReader.Read();
N = sqReader.GetInt32(0);
}
finally
{
sqReader.Close();
}
return N;
}

-----------------------------------------------------

The first version is slow, the second one very fast (~2000 imported records).
Why?
dbMonitor shows that every time the Linq query is executed, a new connection is opened then closed.
Opening ctxt.Database.Connection before entering the loop does not seem to solve the problem.
Is there a workaround?

[Shalex]

DbContext.Database.Connection is not used for the LINQ to Entities queries and SaveChanges(). DbContext is only wrapper of ObjectContext. That's why it is necessary to open ObjectContext's connection.

Replace this line of your code in the second version

Code: Select all

sqLiteCommand1.Connection = (Devart.Data.SQLite.SQLiteConnection) ctxt.Database.Connection;

with

Code: Select all

sqLiteCommand1.Connection = (Devart.Data.SQLite.SQLiteConnection)((IObjectContextAdapter)ctxt).ObjectContext.Connection;

[gwihen]

Thanks a lot.

Remark
sqLiteCommand1.Connection = (Devart.Data.SQLite.SQLiteConnection) ctxt.Database.Connection;
does not work as it raises:

"InvalidCastException
Unable to cast object of type 'System.Data.EntityClient.EntityConnection' to type
'Devart.Data.SQLite.SQLiteConnection"

but, it doesn't matter. It's not the objective.

Instead, add
((IObjectContextAdapter)ctxt).ObjectContext.Connection.Open();
before the loop and the Linq queries are executed without opening each time a new connection.

New code:
------------------------------------------------------------------------
// don't forget: using System.Data.Entity.Infrastructure;


using (Entities ctxt = new Entities())
{
((IObjectContextAdapter)ctxt).ObjectContext.Connection.Open();
foreach (FileInfo fi in arr)
{
SMS rec = new SMS();
rec.FromFile(fi);
var q = from r in ctxt.SMSs
where r.TEL == rec.TEL && r.DATE == rec.DATE
select r;
int N = q.Count();
if (N == 0)
ctxt.SMSs.Add(rec);
}
try
{
ctxt.SaveChanges();
}
catch (Exception ex)
{
// ...
}
}
---------------------------------------------------------------------
It's an interesting improvement.
However, the result is not yet perfect: at each Linq query execution a new SQLite command is created and prepared: this takes some time.

[Shalex]

Try using the Batch Updates feature of dotConnect for SQLite: http://www.devart.com/dotconnect/sqlite ... dates.html. Please remember that SQLite is a local embedded database; that is why, the use of Batch Updates for SQLite does not always guarantee an increase in performance. In some cases, it might actually result in a performance decrease.