Projecting into dynamic fields computed using SQL 'as' keyword

Assume I have the following simplified custom SQL;

var query = "SELECT *, Salary * 2 as Double Salary FROM Employees where Age > 35";

Using LightSpeed 4.0, I can use UnityOfWork.FindBySql(new SqlCommand(query)) to return the list of Employees. This all works great. However, I will also like Double Salary and any other dynamically computed field specified with 'as' to be returned in some sort of IDictionary or similar but your base Entity class does not provide such an API.

I guess one could use UnityOfWork.Project(Query), but I have not yet figured out how to create a Query object using a custom query string.

How should I proceed with this?

TIA.

Klaus

I found a temporary workaround which involved creating a corresponding property for through a partial Employee class. I can then retrieve DoubleSalary and others. This works for now but it would be for the base Entity class to simply return an IDictionary for those fields computed as part of the SELECT statement but are not defined as columns in the database.

I found a temporary workaround which involved creating a corresponding property for through a partial Employee class. I can then retrieve DoubleSalary and others. This works for now but it would be for the base Entity class to simply return an IDictionary for those fields computed as part of the SELECT statement but are not defined as columns in the database.

Hi Klaus,

We only load in properties which correspond to non transient fields on the entity, if you need to load additional data along side the entity you can either do this by adding additional entity properties and having these set with a named aggregate in which case they will only be populated in queries where you specify that aggregate, or additionally you could have these set as transient fields and manually assign them as required.

We dont offer an overload of FindBySql which returns an IDataReader because if you are wanting to issue arbitrary SQL and have a DataReader returned then you should look at using ADO.NET directly as LightSpeed would be offering no value here. You can use the UnitOfWork.PrepareCommand() method in conjunction with this if you want to reuse the connection which is being used by the UnitOfWork instance.

Jeremy