Custom SQL Projected onto a class

Hi,

 I have to do some group-by operations in SQLite which does not support stored procedures thus I wrote my queries in SQL and try to run them using something like this:

using (IDbConnection connection = new SQLiteConnection(Database.Context.ConnectionString)){
connection.Open();
IDbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "my long sql statement";
var reader = command.ExecuteReader();

Now, I'm curious if I can use some utitliy from inside LightSpeed to project the IDataReader directly on my class so I don't have to go through the boring taks of writing new MyResult { Value = reader['Value"] ... }

Thanks,
Corneliu.

We don't have this at the moment, but depending on what you want we could look at adding support for this.  Can you clarify whether your intent is:

1. You will define a specific entity class that corresponds to the results of the custom SQL, and want us to materialise instances of that class (similar to the way stored procedures work); or

2. You want to project the results of the custom SQL onto a subset of the properties of some larger class.

If you didn't have a specific behaviour in mind, let me mention that you're much more likely to get your wish with option 1 than with option 2 *grin*.

Hi Ivan,

Option 1 would be perfect. It would be great if the option would be available on the IUnitOfWork directly so I can do:

using( var context = Datatabase.Instance.CreateUnitOfWork() )
{ 
     var myProducts = context.ExecuteQuery<Product>("my long sql statement");
}

I would like the interface to be IQueryable<T> ExecuteQuery<T> ( string sqlStatement );
or IEnumerable if it's easier.
I'm sure you can do all the dbconnection, dbcommand and execute reader internally to make our life easier :)

Alternatively something like might also be nice though I don't like the idea of strings for column names in there.

from reader in context.ExecuteQuery<Product>("my long sql statement")
select new CustomProductClass
{
     Id = reader["Id"]
     Name = reader["Name"]
}

Regards,
Corneliu.

Okay, that sounds do-able though we do need to discuss it a bit more internally.

Regarding the API, if we do go ahead with this, I think we'll be asking you to supply an IDbCommand rather than a string.  The reason for doing this is to support parameterisation -- if we accepted only a SQL string then you would not be able to use SQL parameters which is obviously important for query plan caching, automating character escaping, avoiding SQL injection, etc.  Yes, this does create a bit more work for you but I think it's worth it!  We do have helper methods for creating the right command objects so this won't be too painful.

It will probably come back as an IList<T> rather than an IQueryable<T> because (a) from our point of view, we do not want to take a dependency on .NET 3.5 in the LightSpeed 2 core (this will change in LightSpeed 3) and (b) from your point of view, there is no meaningful way to compose queries onto the custom SQL (e.g. suppose you wrote context.ExecuteQuery<Product>("/* SQL goes here */").Where(p => p.Price > 100) -- there's no way to merge that Where clause into your custom SQL, so it would have to run client-side anyway).  Seem reasonable?

I'll put this to the rest of the team and will let you know when we have some news for you.

That sounds perfect for me. Maybe a simple method on the unit of work context.CreateDbCommand() that creates for me a command on the current db would be nice so I don't have to do:

Righty ho, this will be in the next nightly build, available from the Downloads page (free edition) or the store (retail editions) from about 1430 GMT.  Here's how to use it.

First, define an entity class that maps onto the result set of the schema:

public class ContributionInfo : Entity<int>
{
  private string _title;

  public string Title
  {
    get { return _title; }
    set { Set(ref _title, value, "Title"); }
  }

  private string _description;

  public string Description
  {
    get { return _description; }
    set { Set(ref _description, value, "Description"); }
  }
}

(or use the designer of course).

Next, create a command by using the LightSpeedContext.DataProviderObjectFactory:

using (IDbCommand cmd = _unitOfWork.Context.DataProviderObjectFactory.CreateCommand())
{
  // The SELECT columns must match the class fields, and must include the Id column
  cmd.CommandText = "SELECT Id, Title, Description FROM Contributions WHERE Id = @id";
  cmd.CommandType = CommandType.Text;

  var parameter = cmd.CreateParameter();
  parameter.ParameterName = "@id";
  parameter.Value = 1;
  cmd.Parameters.Add(parameter);

  // more to come here
}

Finally, call the new FindBySql method to execute the command and load the results as entity instances:

// where it said 'more to come here'
var results = _unitOfWork.FindBySql<ContributionInfo>(cmd);
Assert.AreEqual("Adrenaline Rush", results[0].Title);

LightSpeed will take care of hooking the command up to the connection associated with the unit of work, etc.  You must use the DPOF to ensure that the right kind of command gets created -- if you pass an OracleCommand to a SQLite unit of work then LightSpeed will not fix this for you! -- but using the DPOF means you avoid the dependency in your code.  Of course SQL syntax, function names and parameter prefixes are often database-specific so you may still have a soft dependency.

Note that we only support SELECTs.  We do not provide a way to commit changes back to the database using custom SQL.

This is obviously a first cut of this feature.  It has been tested only on SQL Server and SQLite, though it should work on other databases.  We would welcome bug reports or other feedback -- please let us know how it goes for you.