public async Task FirstOrDefaultPredicateAsync([DataSources] string context)
{
var query = CompiledQuery.Compile <IDataContext, int, CancellationToken, Task <AsyncDataProjection> >((db, id, token) =>
(from c in db.GetTable <AsyncDataTable>()
where c.Id == id
select new AsyncDataProjection
{
Id = id,
Value = c.Id
}).FirstOrDefaultAsync(c => c.Id == id, token));
using (var db = GetDataContext(context))
using (db.CreateLocalTable(GenerateData()))
{
var result = await query(db, 2, CancellationToken.None);
Assert.AreEqual(2, result.Id);
Assert.AreEqual(2, result.Value);
}
}
public Expression GetSubquery(
ExpressionBuilder builder,
Expression expression,
ParameterExpression paramArray,
IEnumerable <Expression> parameters)
{
var lambda = Expression.Lambda <Func <IDataContext, object[], TRet> >(
expression,
Expression.Parameter(typeof(IDataContext), "ctx"),
paramArray);
var queryReader = CompiledQuery.Compile(lambda);
return(Expression.Call(
null,
MemberHelper.MethodOf(() => ExecuteSubQuery(null, null, null)),
DataContextParam,
Expression.NewArrayInit(typeof(object), parameters),
Expression.Constant(queryReader)
));
}
public async Task SingleAsync([IncludeDataSources(true, TestProvName.AllSQLite)] string context)
{
var query = CompiledQuery.Compile <IDataContext, int, CancellationToken, Task <AsyncDataProjection> >((db, id, token) =>
(from c in db.GetTable <AsyncDataTable>()
where c.Id == id
select new AsyncDataProjection
{
Id = id,
Value = c.Id
}).SingleAsync(token));
using (var db = GetDataContext(context))
using (db.CreateLocalTable(GenerateData()))
{
var result = await query(db, 2, CancellationToken.None);
Assert.AreEqual(2, result.Id);
Assert.AreEqual(2, result.Value);
}
}
/// <summary>
/// this method is used to fill the autocomplete of transporter
/// </summary>
/// <param name="companyId"></param>
/// <param name="name"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
public IQueryable <Recognizable> SearchTransporter(Int32 companyId, string name, Int32 maximumRows)
{
string methodName = MethodBase.GetCurrentMethod().ToString();
//if (DataManager.CacheCommands[methodName] == null)
//{
DataManager.CacheCommands[methodName] =
CompiledQuery.Compile <InfoControlDataContext, int, string, int, IQueryable <Recognizable> >(
(ctx, _companyId, _name, _maximumRows) => (from transporter in DbContext.Transporters
join legalEntityProfile in DbContext.LegalEntityProfiles on
transporter.LegalEntityProfileId equals
legalEntityProfile.LegalEntityProfileId
where
transporter.CompanyId == companyId &&
(transporter.LegalEntityProfile.CompanyName.Contains(name) ||
transporter.LegalEntityProfile.FantasyName.Contains(name))
select new Recognizable(transporter.TransporterId.ToString(),
(transporter.LegalEntityProfile.CNPJ) + " | " +
(transporter.LegalEntityProfile.CompanyName))).Take(maximumRows));
// query = query.Take(maximumRows);
// DataManager.CacheCommands[methodName] = DbContext.GetCommand(query);
//}
var method =
(Func <InfoControlDataContext, int, string, int, IQueryable <Recognizable> >)
DataManager.CacheCommands[methodName];
return(method(DbContext, companyId, name, maximumRows));
//DataReader reader = DataManager.ExecuteCachedQuery(methodName, " | ", companyId, "%" + name + "%",
// "%" + name + "%");
//var list = new List<string>();
//while (reader.Read())
//{
// list.Add(reader.GetString(0));
//}
//return list.ToArray();
}
/// <summary>
/// Precompiled Queries example
/// </summary>
private void CompiledQueries()
{
var dc = new NorthwindDataContext();
//Precompile Query to retrieve customers in USA
var usaCustomers = CompiledQuery.Compile((NorthwindDataContext ctx) => ctx.Customers
.Where(c => c.Country == "USA")
.Select(c => new
{
c.CompanyName,
c.City,
c.Region
}));
"Customers in the USA".DisplayHeader();
foreach (var cust in usaCustomers(dc))
{
string.Format("{0} in {1}, {2}", cust.CompanyName, cust.City, cust.Region).DisplayResults();
}
}
protected SQLCacheDataAccessor(Dictionary <string, byte[]> metadataIfCreation, int maxCacheSize, CacheVersionParameters cacheVersionParameters, string[] EntityConnectionMetadataPaths)
{
this.metadataIfCreation = metadataIfCreation;
this.MaxCacheSize = maxCacheSize;
this.EntityConnectionMetadataPaths = EntityConnectionMetadataPaths;
this.cacheVersion = cacheVersionParameters;
#if COMPILED_QUERIES
this.compiledGetMethod = CompiledQuery.Compile <ClousotCacheEntities, byte[], MethodModel>(
(context, hash) => context.MethodModels.FirstOrDefault(m => m.Hash == hash));
this.compiledGetBinding = CompiledQuery.Compile <ClousotCacheEntities, long, Guid, bool>(
(context, methodid, guid) => context.AssemblyBindings.Any(b => b.MethodId == methodid && b.AssemblyId == guid));
this.compiledNbToDelete = CompiledQuery.Compile <ClousotCacheEntities, int>(
context => context.MethodModels.Count() - this.MaxCacheSize);
#else
this.compiledGetMethod = (context, hash) => context.MethodModels.FirstOrDefault(m => m.Hash == hash);
this.compiledGetBinding = (context, methodid, guid) => context.AssemblyBindings.Any(b => b.MethodId == methodid && b.AssemblyId == guid);
this.compiledNbToDelete = (context) => context.MethodModels.Count() - this.MaxCacheSize;
#endif
}
public void Setup()
{
var schema = new DataTable();
schema.Columns.Add("AllowDBNull", typeof(bool));
schema.Rows.Add(false);
schema.Rows.Add(true);
schema.Rows.Add(true);
var result = new QueryResult()
{
Schema = schema,
Names = new[] { "id", "name", "login_count" },
FieldTypes = new[] { typeof(long), typeof(string), typeof(int) },
DbTypes = new[] { "int8", "varchar", "int4" },
Data = new object?[][] { new object?[] { 100500L, "Vasily Lohankin", 123 } },
};
_cn = new MockDbConnection(result, ConnectionState.Open);
_compiled = CompiledQuery.Compile <DataConnection, long?, IQueryable <User> >(
(db, userId) => from c in db.GetTable <User>()
where userId == null || c.Id == userId
select c);
var threadCount = ThreadCount;
_threads = new Thread[threadCount];
_db = new DataConnection[threadCount];
for (var i = 0; i < _threads.Length; i++)
{
_db[i] = new DataConnection(new PostgreSQLDataProvider(PostgreSQLVersion.v95), _cn);
_threads[i] = new Thread(ThreadWorker);
_threads[i].IsBackground = true; // we don't stop threads explicitly
_threads[i].Start(i);
}
}
public void ConcurentTest1(string context)
{
var query = CompiledQuery.Compile((ITestDataContext db, int n) =>
db.GetTable <Parent>().Where(p => p.ParentID == n).First().ParentID);
const int count = 100;
var threads = new Thread[count];
var results = new int [count, 2];
for (var i = 0; i < count; i++)
{
var n = i;
threads[i] = new Thread(() =>
{
using (var db = GetDataContext(context))
{
var id = (n % 6) + 1;
results[n, 0] = id;
results[n, 1] = query(db, id);
}
});
}
for (var i = 0; i < count; i++)
{
threads[i].Start();
}
for (var i = 0; i < count; i++)
{
threads[i].Join();
}
for (var i = 0; i < count; i++)
{
Assert.AreEqual(results[i, 0], results[i, 1]);
}
}
public HighScoreDatabaseRepository()
{
using (IsolatedStorageFile storage = IsolatedStorageFile.GetUserStoreForApplication())
{
if (!storage.DirectoryExists("HighScoreDatabase"))
{
storage.CreateDirectory("HighScoreDatabase");
}
}
db = new HighScoresDataContext(@"isostore:/HighScoreDatabase/highscores.sdf");
if (!db.DatabaseExists())
{
db.CreateDatabase();
DatabaseSchemaUpdater updater = db.CreateDatabaseSchemaUpdater();
updater.DatabaseSchemaVersion = 1;
updater.Execute();
}
else
{
DatabaseSchemaUpdater updater = db.CreateDatabaseSchemaUpdater();
int databaseSchemaVersion = updater.DatabaseSchemaVersion;
if (databaseSchemaVersion == 0)
{
// add the difficulty column introduced in version one
updater.AddColumn <HighScore>("Difficulty");
updater.DatabaseSchemaVersion = 1;
updater.Execute();
}
}
allQuery = CompiledQuery.Compile((HighScoresDataContext context) => from score in db.HighScores
orderby score.Score descending
select score);
levelQuery = CompiledQuery.Compile((HighScoresDataContext context, int level) => from score in db.HighScores
orderby score.Score descending
where score.LevelsCompleted == level
select score);
}
public PlayersModel(TennisUserModel tennisUserP, SportsLinkDB dbP)
: base(tennisUserP)
{
if (null == CachedQuery)
{
var tennisUsers = ModelUtils.GetTennisUsersFunc();
Expression <Func <SportsLinkDB, TennisUserModel, IQueryable <TennisUserModel> > > players =
(SportsLinkDB db, TennisUserModel tennisUser) => tennisUsers.Invoke(db).Where
(p =>
Math.Abs(p.Rating - tennisUser.Rating) <= 0.25 &&
db.CoordinateDistanceMiles(p.City.Latitude, p.City.Longitude, tennisUser.City.Latitude, tennisUser.City.Longitude) < 15 &&
p.FacebookId != tennisUser.FacebookId &&
p.Gender == tennisUser.Gender
);
CachedQuery = CompiledQuery.Compile <SportsLinkDB, TennisUserModel, IQueryable <TennisUserModel> >
(
players.Expand()
);
}
this.Players = CachedQuery(dbP, tennisUserP);
}
static QueriesCompiled()
{
GetCustomHostnameQuery = CompiledQuery.Compile <tradelrDataContext, string, MASTERsubdomain>(
(db, hostname) => db.MASTERsubdomains.SingleOrDefault(x => x.customDomain == hostname)
);
GetSubDomainQuery = CompiledQuery.Compile <tradelrDataContext, string, MASTERsubdomain>(
(db, name) => db.MASTERsubdomains.SingleOrDefault(x => x.name == name)
);
GetSubDomainByIDQuery = CompiledQuery.Compile <tradelrDataContext, long, MASTERsubdomain>(
(db, id) => db.MASTERsubdomains.SingleOrDefault(x => x.id == id)
);
GetUserByIdQuery = CompiledQuery.Compile <tradelrDataContext, long, long, user>(
(db, id, subdomain) =>
db.users.SingleOrDefault(x => x.id == id && x.organisation1.subdomain == subdomain)
);
GetUserByPasswordHashQuery = CompiledQuery.Compile <tradelrDataContext, string, long, user>(
(db, hash, subdomainid) =>
db.users.SingleOrDefault(x => x.passwordHash == hash && x.organisation1.subdomain == subdomainid)
);
}
public UserOffersModel(TennisUserModel tennisUserP, SportsLinkDB dbP)
: base(tennisUserP)
{
// BUGBUG: what about offers which were not confirmed and where the offer time is past - we need to eliminate those from the db
if (null == CachedQuery)
{
var offers = ModelUtils.GetOffersFunc();
Expression <Func <SportsLinkDB, TennisUserModel, IQueryable <OfferModel> > > results =
(SportsLinkDB db, TennisUserModel tennisUser) =>
offers.Invoke(db, tennisUser)
.Where(o => o.ConfirmedUser == null)
.Where(o => o.RequestUser.FacebookId == tennisUser.FacebookId && o.MatchDateUtc > DateTime.UtcNow)
.OrderBy(o => o.MatchDateUtc);
CachedQuery = CompiledQuery.Compile <SportsLinkDB, TennisUserModel, IQueryable <OfferModel> >
(
results.Expand()
);
}
this.UserOffers = CachedQuery(dbP, tennisUserP);
}
public IgniteSqlBenchmark()
{
var fieldsQuery = ((ICacheQueryable)_cache.AsCacheQueryable()
.Select(x => x.Value.Id)
.Where(x => x > SqlDb.IdMin && x < SqlDb.IdMax)).GetFieldsQuery();
Console.WriteLine(fieldsQuery.Sql);
var c = _cache.Query(
new SqlFieldsQuery(
"select _T0.Id from \"persons\".Person as _T0 where ((_T0.Id > ?) and (_T0.Id < ?))", SqlDb.IdMin,
SqlDb.IdMax)).GetAll();
Console.WriteLine(c.Count);
Console.WriteLine(_cache.Query(fieldsQuery).GetAll().Count);
_qry = CompiledQuery.Compile((int min, int max) => _cache.AsCacheQueryable()
.Select(x => x.Value.Id)
.Where(x => x > min && x < max));
Console.WriteLine(_qry(SqlDb.IdMin, SqlDb.IdMax).GetAll().Count);
}
public ResultsModel(TennisUserModel tennisUserP, SportsLinkDB dbP)
: base(tennisUserP)
{
if (null == CachedQuery)
{
var offers = ModelUtils.GetOffersFunc();
Expression <Func <SportsLinkDB, TennisUserModel, IQueryable <OfferModel> > > results =
(SportsLinkDB db, TennisUserModel tennisUser) =>
offers.Invoke(db, tennisUser)
.Where(o => o.ConfirmedUser != null)
.Where(o => (o.ConfirmedUser.FacebookId == tennisUser.FacebookId || o.RequestUser.FacebookId == tennisUser.FacebookId))
.Where(o => (o.Score != null && o.Score != ""))
.OrderByDescending(o => o.MatchDateUtc);
CachedQuery = CompiledQuery.Compile <SportsLinkDB, TennisUserModel, IQueryable <OfferModel> >
(
results.Expand()
);
}
this.UserResults = CachedQuery(dbP, tennisUserP);
}
public void Setup()
{
_cn = new MockDbConnection(new QueryResult()
{
Return = 1
}, ConnectionState.Open);
_db = new DataConnection(new PostgreSQLDataProvider(PostgreSQLVersion.v95), _cn);
_compiledLinqSet = CompiledQuery.Compile <DataConnection, Workflow, int>(
(db, record) => db.GetTable <Workflow>()
.Where(x => x.Id == record.Id && x.RowVersion == record.RowVersion)
.Set(x => x.Status, record.Status)
.Set(x => x.Result, record.Result)
.Set(x => x.Error, record.Error)
.Set(x => x.Steps, record.Steps)
.Set(x => x.UpdateTime, record.UpdateTime)
.Set(x => x.RowVersion, x => x.RowVersion + 1)
.Set(x => x.StartTime, record.StartTime)
.Set(x => x.ProcessedTime, record.ProcessedTime)
.Set(x => x.CompleteTime, record.CompleteTime)
.Update());
_compiledLinqObject = CompiledQuery.Compile <DataConnection, Workflow, int>(
(db, record) => db.GetTable <Workflow>()
.Where(x => x.Id == _record.Id && x.RowVersion == _record.RowVersion)
.Update(x => new()
{
Status = record.Status,
Result = record.Result,
Error = record.Error,
Steps = record.Steps,
UpdateTime = record.UpdateTime,
RowVersion = x.RowVersion + 1,
StartTime = record.StartTime,
ProcessedTime = record.ProcessedTime,
CompleteTime = record.CompleteTime
}));
}
public bool SimpleLinqQuery(Stopwatch watch, int repeatCount, int takeCount)
{
var query = CompiledQuery.Compile((BLTContext db, int top) =>
(
from n1 in db.Narrows
where n1.ID < 100
select n1.ID
)
.Take(top));
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new BLTContext())
foreach (var item in query(db, takeCount))
{
}
}
watch.Stop();
return(true);
}
public void TestCompiledQueryFreeform()
{
var cache = GetPersonCache().AsCacheQueryable();
var qry = cache.Where(x => x.Key < 5);
// Simple
var compiled = CompiledQuery.Compile(qry);
Assert.AreEqual(5, compiled(5).Count());
Assert.AreEqual(6, compiled(6).Count());
// Select
var compiledSelect = CompiledQuery.Compile(qry.Select(x => x.Value.Name).OrderBy(x => x));
Assert.AreEqual(3, compiledSelect(3).Count());
Assert.AreEqual(" Person_0 ", compiledSelect(1).Single());
// Join
var compiledJoin = CompiledQuery.Compile(qry.Join(
GetOrgCache().AsCacheQueryable().Where(x => x.Value.Name.StartsWith("Org")),
p => p.Value.OrganizationId, o => o.Value.Id, (p, o) => o.Key));
Assert.AreEqual(1000, compiledJoin("Org", 1).Single());
// Many parameters
var qry2 = cache.Where(x => x.Key < 3)
.Where(x => x.Key > 0)
.Where(x => x.Value.Name.Contains(""))
.Where(x => x.Value.Address.Zip > 0)
.Where(x => x.Value.Age == 7);
var compiled2 = CompiledQuery.Compile(qry2);
Assert.AreEqual(17, compiled2(18, 16, "ers", 13, 17).Single().Key);
}
public void Setup()
{
_cn = new MockDbConnection(new QueryResult()
{
Return = 1
}, ConnectionState.Open);
_db = new DataConnection(new SqlServerDataProvider(ProviderName.SqlServer, SqlServerVersion.v2008, SqlServerProvider.MicrosoftDataSqlClient), _cn);
_compiled = CompiledQuery.Compile <DataConnection, int, int>((ctx, i) =>
ctx.GetTable <MyPOCO>()
.Where(p => p.Code == "A" + i && p.Currency == "SUR")
.Set(p => p.Weight, i * 10)
.Set(p => p.Currency, "SUR")
.Set(p => p.Value, i * i + 2)
.Update());
_compiledNullable = CompiledQuery.Compile <DataConnection, int, int>((ctx, i) =>
ctx.GetTable <MyPOCON>()
.Where(p => p.Code == "A" + i && p.Currency == "SUR")
.Set(p => p.Weight, i * 10)
.Set(p => p.Currency, "SUR")
.Set(p => p.Value, i * i + 2)
.Update());
}
public static void FillUnitsTable()
{
//Obtaining the data source
LoggingContext dbEventLog = new LoggingContext();
int OtherDerivedUnitClassSize = OtherDerivedUnitStrings.Count;
//int DeleteClassSize = BaseUnitsClassSize + NamedDerivedUnitsClassSize + OtherDerivedUnitClassSize;
int DeleteClassSize = OtherDerivedUnitBaseNumber + OtherDerivedUnitClassSize;
// Remove all units from dbEventLog.Units
//Create compiled query
var fnUnitsOfClass = CompiledQuery.Compile((MeasurementsDataContext dbEventLog1, int UnitClass, int ClassSize) =>
from c in dbEventLog1.Units
where c.Id >= UnitClass
where c.Id < (UnitClass + ClassSize)
select c);
using (IDbContextTransaction ts = dbEventLog.Database.BeginTransaction())
{
try
{
// Execute the query
// Remove all units from dbEventLog.Units
dbEventLog.Units.RemoveRange(dbEventLog.Units);
dbEventLog.SaveChanges();
// SET IDENTITY_INSERT Units ON to try to avoid exception in final dbEventLog.SaveChanges(); Entity framwork core SqlException: Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.
//dbEventLog.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Units ON");
// Fill base units into dbEventLog.Units
foreach (PhysicalMeasure.BaseUnit pu in PhysicalMeasure.Physics.SI_Units.BaseUnits)
{
UnitDBItem u = new UnitDBItem()
{
Id = BaseUnitBaseNumber + pu.BaseUnitNumber, Name = pu.Name, Symbol = pu.Symbol, Exponents = pu.Exponents
};
dbEventLog.Units.Add(u);
}
// Fill named derived units into dbEventLog.Units
int NamedDerivedUnitIndex = 0;
foreach (PhysicalMeasure.NamedDerivedUnit pu in PhysicalMeasure.Physics.SI_Units.NamedDerivedUnits)
{
UnitDBItem u = new UnitDBItem()
{
Id = NamedDerivedUnitBaseNumber + NamedDerivedUnitIndex, Name = pu.Name, Symbol = pu.Symbol, Exponents = pu.Exponents
};
dbEventLog.Units.Add(u);
NamedDerivedUnitIndex++;
}
// Fill Convertible units into dbEventLog.Units
int NamedConvertibleUnitIndex = 0;
foreach (PhysicalMeasure.ConvertibleUnit pu in PhysicalMeasure.Physics.SI_Units.ConvertibleUnits)
{
UnitDBItem u = new UnitDBItem()
{
Id = NamedConvertibleUnitBaseNumber + NamedConvertibleUnitIndex, Name = pu.Name, Symbol = pu.Symbol, Exponents = pu.Exponents, ConversionFactor = pu.Conversion.LinearScale, ConversionOffset = pu.Conversion.LinearOffset
};
dbEventLog.Units.Add(u);
NamedConvertibleUnitIndex++;
}
// Fill named derived units into dbEventLog.Units
int OtherDerivedUnitIndex = 0;
foreach (string unitStr in OtherDerivedUnitStrings)
{
PhysicalUnit pu = PhysicalUnit.Parse(unitStr);
PhysicalQuantity pq = pu.ConvertToSystemUnit().ConvertToDerivedUnit();
UnitDBItem u = new UnitDBItem()
{
Id = OtherDerivedUnitBaseNumber + OtherDerivedUnitIndex, Name = pu.ToPrintString(), Exponents = pq.Unit.Exponents, ConversionFactor = 1 / pq.Value
};
dbEventLog.Units.Add(u);
OtherDerivedUnitIndex++;
}
// Entity framwork core SqlException: Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.
// dbEventLog.Database.GetDbConnection();
//dbEventLog.ChangeTracker.Context.
// dbEventLog.ChangeTracker.QueryTrackingBehavior
// dbEventLog.
dbEventLog.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Units ON");
dbEventLog.SaveChanges();
ts.Commit();
}
catch (Exception ex)
{
InternalErrorDBItem.ApplicationInternalErrorLog("Error when FillUnitsTable. " + ex.Message);
ts.Rollback();
// throw;
}
}
}
public void Run(int iterations)
{
using (var connection = TestSuite.GetOpenConnection())
{
var tests = new Tests();
#if LINQ2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq 2 SQL Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq 2 SQL ExecuteQuery");
}, "LINQ-to-SQL");
#endif
#if ENTITY_FRAMEWORK
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity framework SqlQuery");
//var entityContext3 = new EFContext(connection);
//tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery");
//var entityContext4 = new EFContext(connection);
//tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL");
var entityContext5 = new EFContext(connection);
tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity framework No Tracking");
}, "Entity Framework");
#endif
Try(() =>
{
var mapperConnection = TestSuite.GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Mapper Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Mapper Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Mapper QueryFirstOrDefault");
var mapperConnection2 = TestSuite.GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dynamic Mapper Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dynamic Mapper Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dynamic Mapper QueryQueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = TestSuite.GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
#if MASSIVE
Try(() =>
{
// massive
var massiveModel = new DynamicModel(TestSuite.ConnectionString);
var massiveConnection = TestSuite.GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Dynamic Massive ORM Query");
}, "Massive");
#endif
#if PETAPOCO
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(TestSuite.ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Normal)");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(TestSuite.ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Fast)");
}, "PetaPoco");
#endif
#if SUBSONIC
Try(() =>
{
// Subsonic ActiveRecord
tests.Add(id => SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// Subsonic coding horror
SubSonic.tempdbDB db = new SubSonic.tempdbDB();
tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList <Post>(), "SubSonic Coding Horror");
}, "Subsonic");
#endif
// NHibernate
#if NHIBERNATE
Try(() => {
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate Session.Get");
}, "NHibernate");
#endif
#if BLTOOLKIT
// bltoolkit
var db1 = new DbManager(TestSuite.GetOpenConnection());
tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList <Post>(), "BLToolkit");
#endif
#if SIMPLEDATA
// Simple.Data
Try(() =>
{
var sdb = Simple.Data.Database.OpenConnection(TestSuite.ConnectionString);
tests.Add(id => sdb.Posts.FindById(id), "Simple.Data");
}, "Simple.Data");
#endif
#if BELGRADE
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(TestSuite.GetOpenConnection());
tests.Add(id => query.ExecuteReader("SELECT TOP 1 * FROM Posts WHERE Id = " + id,
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
#endif
#if SUSANOO
//Susanoo
var susanooDb = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
var susanooDb2 = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
var susanooPreDefinedCommand =
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize("PostById");
var susanooDynamicPreDefinedCommand =
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize("DynamicById");
tests.Add(Id =>
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize("PostById")
.Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize("DynamicById")
.Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Cache Retrieval");
tests.Add(Id =>
susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Static");
tests.Add(Id =>
susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Static");
#endif
#if SOMA
// Soma
// DISABLED: assembly fail loading FSharp.PowerPack, Version=2.0.0.0
// var somadb = new Soma.Core.Db(new SomaConfig());
// tests.Add(id => somadb.Find<Post>(id), "Soma");
#endif
#if ORMLITE
//ServiceStack's OrmLite:
// DISABLED: can't find QueryById
//OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
//IDbCommand ormLiteCmd = TestSuite.GetOpenConnection().CreateCommand();
// tests.Add(id => ormLiteCmd.QueryById<Post>(id), "OrmLite QueryById");
#endif
// HAND CODED
var postCommand = new SqlCommand();
postCommand.Connection = connection;
postCommand.CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id";
var idParam = postCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "hand coded");
#if !COREFX
DataTable table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
#endif
Console.WriteLine();
Console.WriteLine("Running...");
tests.Run(iterations);
}
}
public Expression GetGroupJoin(GroupJoinContext context)
{
// Convert outer condition.
//
var outerParam = Expression.Parameter(context._outerKeyLambda.Body.Type, "o");
var outerKey = context._outerKeyLambda.GetBody(context.Lambda.Parameters[0]);
outerKey = context.Builder.BuildExpression(context, outerKey);
// Convert inner condition.
//
var parameters = context.Builder.CurrentSqlParameters
.Select((p, i) => new { p, i })
.ToDictionary(_ => _.p.Expression, _ => _.i);
var paramArray = Expression.Parameter(typeof(object[]), "ps");
var innerKey = context._innerKeyLambda.Body.Transform(e =>
{
int idx;
if (parameters.TryGetValue(e, out idx))
{
return
(Expression.Convert(
Expression.ArrayIndex(paramArray, Expression.Constant(idx)),
e.Type));
}
return(e);
});
// Item reader.
//
// ReSharper disable AssignNullToNotNullAttribute
var expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Where(null, (Expression <Func <TElement, bool> >)null)),
context._innerExpression,
Expression.Lambda <Func <TElement, bool> >(
Expression.Equal(innerKey, outerParam),
new[] { context._innerKeyLambda.Parameters[0] }));
// ReSharper restore AssignNullToNotNullAttribute
var lambda = Expression.Lambda <Func <IDataContext, TKey, object[], IQueryable <TElement> > >(
Expression.Convert(expr, typeof(IQueryable <TElement>)),
Expression.Parameter(typeof(IDataContext), "ctx"),
outerParam,
paramArray);
var itemReader = CompiledQuery.Compile(lambda);
return(Expression.Call(
null,
MemberHelper.MethodOf(() => GetGrouping(null, null, default(TKey), null)),
new[]
{
ExpressionBuilder.ContextParam,
Expression.Constant(context.Builder.CurrentSqlParameters),
outerKey,
Expression.Constant(itemReader),
}));
}
public async Task RunAsync(int iterations)
{
using (var connection = GetOpenConnection())
{
#pragma warning disable IDE0017 // Simplify object initialization
#pragma warning disable RCS1121 // Use [] instead of calling 'First'.
var tests = new Tests();
// Entity Framework Core
Try(() =>
{
var entityContext = new EFCoreContext(ConnectionString);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework Core");
var entityContext2 = new EFCoreContext(ConnectionString);
tests.Add(id => entityContext2.Posts.FromSql("select * from Posts where Id = {0}", id).First(), "Entity Framework Core: FromSql");
var entityContext3 = new EFCoreContext(ConnectionString);
tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework Core: No Tracking");
}, "Entity Framework Core");
// Dapper
Try(() =>
{
var mapperConnection = GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault");
var mapperConnection2 = GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
// Massive
Try(() =>
{
var massiveModel = new DynamicModel(ConnectionString);
var massiveConnection = GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query");
}, "Massive");
// PetaPoco
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast");
}, "PetaPoco");
// NHibernate
Try(() =>
{
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery("select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery("from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate: Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate: LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate: Session.Get");
}, "NHibernate");
// Belgrade
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString);
tests.AsyncAdd(id => query.Sql("SELECT TOP 1 * FROM Posts WHERE Id = @Id").Param("Id", id).Map(
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
//ServiceStack's OrmLite:
Try(() =>
{
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
var db = dbFactory.Open();
tests.Add(id => db.SingleById <Post>(id), "ServiceStack.OrmLite: SingleById");
}, "ServiceStack.OrmLite");
// Hand Coded
Try(() =>
{
var postCommand = new SqlCommand()
{
Connection = connection,
CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"
};
var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "Hand Coded");
var table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
}, "Hand Coded");
// DevExpress.XPO
Try(() =>
{
IDataLayer dataLayer = XpoDefault.GetDataLayer(connection, DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists);
dataLayer.Dictionary.GetDataStoreSchema(typeof(Xpo.Post));
UnitOfWork session = new UnitOfWork(dataLayer, dataLayer);
session.IdentityMapBehavior = IdentityMapBehavior.Strong;
session.TypesManager.EnsureIsTypedObjectValid();
tests.Add(id => session.Query <Xpo.Post>().First(p => p.Id == id), "DevExpress.XPO: Query<T>");
tests.Add(id => session.GetObjectByKey <Xpo.Post>(id, true), "DevExpress.XPO: GetObjectByKey<T>");
tests.Add(id =>
{
CriteriaOperator findCriteria = new BinaryOperator()
{
OperatorType = BinaryOperatorType.Equal,
LeftOperand = new OperandProperty("Id"),
RightOperand = new ConstantValue(id)
};
session.FindObject <Xpo.Post>(findCriteria);
}, "DevExpress.XPO: FindObject<T>");
}, "DevExpress.XPO");
// Entity Framework
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery");
var entityContext3 = new EFContext(connection);
tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking");
}, "Entity Framework");
#if NET4X
// Linq2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery");
}, "LINQ-to-SQL");
// Dashing
Try(() =>
{
var config = new DashingConfiguration();
var database = new SqlDatabase(config, ConnectionString);
var session = database.BeginTransactionLessSession(GetOpenConnection());
tests.Add(id => session.Get <Dashing.Post>(id), "Dashing Get");
}, "Dashing");
//Susanoo
Try(() =>
{
var susanooDb = new DatabaseManager(connection);
var susanooPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize();
var susanooDynamicPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize();
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval");
tests.Add(Id => susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static");
tests.Add(Id => susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static");
}, "Susanoo");
#endif
// Subsonic isn't maintained anymore - doesn't import correctly
//Try(() =>
// {
// // Subsonic ActiveRecord
// tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// // Subsonic coding horror
// SubSonic.tempdbDB db = new SubSonic.tempdbDB();
// tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
//}, "Subsonic");
//// BLToolkit - doesn't import correctly in the new .csproj world
//var db1 = new DbManager(GetOpenConnection());
//tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
Console.WriteLine();
Console.WriteLine("Running...");
await tests.RunAsync(iterations).ConfigureAwait(false);
#pragma warning restore RCS1121 // Use [] instead of calling 'First'.
#pragma warning restore IDE0017 // Simplify object initialization
}
}
private static TResult GetValue <TDataContext, TTableType, TKey, TResult>(
TDataContext dataContext,
TKey value,
Expression <Func <TDataContext, TTableType, TKey, bool> > whereExp,
Expression <Func <TDataContext, TTableType, TKey, TResult> > selectExp,
bool useCacheValue,
out string keyQuery,
out string keyResult,
params ExpressionCulture <Func <TDataContext, TTableType, TKey, TResult> >[] selectExpForCultures)
where TTableType : class
where TDataContext : DataContext
{
var currentCulture = Thread.CurrentThread.CurrentUICulture.Name;
selectExp = selectExpForCultures
.Where(r => r.CultureName.Equals(currentCulture, StringComparison.OrdinalIgnoreCase))
.Select(r => r.Expression)
.FirstOrDefault() ?? selectExp;
keyQuery = dataContext.GetType().FullName
+ "#" + typeof(TTableType).FullName
+ "#" + typeof(TResult).FullName
+ "#" + typeof(TKey).FullName
+ "#" + typeof(TDataContext).FullName
+ "#" + whereExp
+ "#" + selectExp;
keyResult = keyQuery + "#" + value;
if (value == null)
{
return(default(TResult));
}
Func <TDataContext, TKey, TResult> func;
if (HttpContext.Current != null || SimpleCache != null)
{
if (useCacheValue)
{
var obj = HttpContext.Current != null ? HttpContext.Current.Cache[keyResult] : SimpleCache[keyResult];
if (obj == DBNull.Value)
{
return(default(TResult));
}
if (obj != null)
{
return((TResult)obj);
}
}
func = (Func <TDataContext, TKey, TResult>)
(HttpContext.Current != null ? HttpContext.Current.Cache[keyQuery] : SimpleCache[keyQuery]);
if (func != null)
{
return(func(dataContext, value));
}
}
var paramDc = Expression.Parameter(typeof(TDataContext), "dcnc");
var paramT = Expression.Parameter(typeof(TTableType), "tnc");
var paramKey = Expression.Parameter(typeof(TKey), "tnk");
var exp = Expression.Invoke(whereExp, paramDc, paramT, paramKey);
var whereT = Expression.Lambda <Func <TTableType, bool> >(exp, paramT);
exp = Expression.Invoke(selectExp, paramDc, paramT, paramKey);
var selectT = Expression.Lambda <Func <TTableType, TResult> >(exp, paramT);
Expression <Func <TDataContext, IQueryable <TTableType> > > getTable =
dc => (IQueryable <TTableType>)dc.GetTable <TTableType>();
Expression resultExp = Expression.Invoke(getTable, paramDc);
resultExp = Expression.Call(typeof(Queryable), "Where", new[] { typeof(TTableType) }, resultExp, whereT);
resultExp = Expression.Call(
typeof(Queryable), "Select", new[] { typeof(TTableType), typeof(TResult) }, resultExp, selectT);
resultExp = Expression.Call(typeof(Queryable), "FirstOrDefault", new[] { typeof(TResult) }, resultExp);
var resultLambda = Expression.Lambda <Func <TDataContext, TKey, TResult> >(resultExp, paramDc, paramKey);
func = CompiledQuery.Compile(resultLambda);
var result = func(dataContext, value);
if (HttpContext.Current != null)
{
HttpContext.Current.Cache[keyQuery] = func;
}
return(result);
}
public Expression GetGrouping(GroupByContext context)
{
if (Configuration.Linq.GuardGrouping && !context._isGroupingGuardDisabled)
{
if (context.Element.Lambda.Parameters.Count == 1 &&
context.Element.Body == context.Element.Lambda.Parameters[0])
{
var ex = new LinqToDBException(
"You should explicitly specify selected fields for server-side GroupBy() call or add AsEnumerable() call before GroupBy() to perform client-side grouping.\n" +
"Set Configuration.Linq.GuardGrouping = false to disable this check.\n" +
"Additionally this guard exception can be disabled by extension GroupBy(...).DisableGuard().\n" +
"NOTE! By disabling this guard you accept additional Database Connection(s) to the server for processing such requests."
)
{
HelpLink = "https://github.com/linq2db/linq2db/issues/365"
};
throw ex;
}
}
var parameters = context.Builder.CurrentSqlParameters
.Select((p, i) => new { p, i })
.ToDictionary(_ => _.p.Expression, _ => _.i);
var paramArray = Expression.Parameter(typeof(object[]), "ps");
var groupExpression = context._sequenceExpr.Transform(e =>
{
if (parameters.TryGetValue(e, out var idx))
{
return
(Expression.Convert(
Expression.ArrayIndex(paramArray, Expression.Constant(idx)),
e.Type));
}
return(e);
});
var keyParam = Expression.Parameter(typeof(TKey), "key");
// ReSharper disable AssignNullToNotNullAttribute
var expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Where(null, (Expression <Func <TSource, bool> >)null !)),
groupExpression,
Expression.Lambda <Func <TSource, bool> >(
ExpressionBuilder.Equal(context.Builder.MappingSchema, context._key.Lambda.Body, keyParam),
new[] { context._key.Lambda.Parameters[0] }));
expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Select(null, (Expression <Func <TSource, TElement> >)null !)),
expr,
context.Element.Lambda);
// ReSharper restore AssignNullToNotNullAttribute
var lambda = Expression.Lambda <Func <IDataContext, TKey, object?[]?, IQueryable <TElement> > >(
Expression.Convert(expr, typeof(IQueryable <TElement>)),
Expression.Parameter(typeof(IDataContext), "ctx"),
keyParam,
paramArray);
var itemReader = CompiledQuery.Compile(lambda);
var keyExpr = context._key.BuildExpression(null, 0, false);
var keyReader = Expression.Lambda <Func <IQueryRunner, IDataContext, IDataReader, Expression, object?[]?, TKey> >(
keyExpr,
new []
{
ExpressionBuilder.QueryRunnerParam,
ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam,
ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam
});
return(Expression.Call(
null,
MemberHelper.MethodOf(() => GetGrouping(null !, null !, null !, null !, null !, null !, null !, null !)),
new Expression[]
{
ExpressionBuilder.QueryRunnerParam,
ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam,
Expression.Constant(context.Builder.CurrentSqlParameters),
ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam,
keyReader,
Expression.Constant(itemReader)
}));
}
public async Task RunAsync(int iterations)
{
using (var connection = GetOpenConnection())
{
var tests = new Tests();
// Linq2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery");
}, "LINQ-to-SQL");
// Entity Framework
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery");
//var entityContext3 = new EFContext(connection);
//tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity Framework CompiledQuery");
//var entityContext4 = new EFContext(connection);
//tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity Framework ESQL");
var entityContext5 = new EFContext(connection);
tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking");
}, "Entity Framework");
// Dapper
Try(() =>
{
var mapperConnection = GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault");
var mapperConnection2 = GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
// Massive
Try(() =>
{
var massiveModel = new DynamicModel(ConnectionString);
var massiveConnection = GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query");
}, "Massive");
// PetaPoco
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast");
}, "PetaPoco");
// NHibernate
Try(() =>
{
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate: Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate: LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate: Session.Get");
}, "NHibernate");
// Simple.Data
Try(() =>
{
var sdb = Simple.Data.Database.OpenConnection(ConnectionString);
tests.Add(id => sdb.Posts.FindById(id).FirstOrDefault(), "Simple.Data");
}, "Simple.Data");
// Belgrade
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString);
tests.AsyncAdd(id => query.ExecuteReader("SELECT TOP 1 * FROM Posts WHERE Id = " + id,
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
//Susanoo
var susanooDb = new DatabaseManager(connection);
var susanooPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize();
var susanooDynamicPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize();
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval");
tests.Add(Id => susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static");
tests.Add(Id => susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static");
//ServiceStack's OrmLite:
Try(() =>
{
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
var db = dbFactory.Open();
tests.Add(id => db.SingleById <Post>(id), "ServiceStack.OrmLite: SingleById");
}, "ServiceStack.OrmLite");
// Hand Coded
var postCommand = new SqlCommand()
{
Connection = connection,
CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"
};
var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "Hand Coded");
// Subsonic isn't maintained anymore - doesn't import correctly
//Try(() =>
// {
// // Subsonic ActiveRecord
// tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// // Subsonic coding horror
// SubSonic.tempdbDB db = new SubSonic.tempdbDB();
// tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
//}, "Subsonic");
//// BLToolkit - doesn't import correctly in the new .csproj world
//var db1 = new DbManager(GetOpenConnection());
//tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
#if !COREFX
var table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
#endif
Console.WriteLine();
Console.WriteLine("Running...");
await tests.RunAsync(iterations).ConfigureAwait(false);
}
}
public Expression GetGrouping(GroupByContext context)
{
if (Configuration.Linq.GuardGrouping)
{
if (context._element.Lambda.Parameters.Count == 1 &&
context._element.Body == context._element.Lambda.Parameters[0])
{
var ex = new LinqToDBException(
"You should explicitly specify selected fields for server-side GroupBy() call or add AsEnumerable() call before GroupBy() to perform client-side grouping.\n" +
"Set Configuration.Linq.GuardGrouping = false to disable this check."
)
{
HelpLink = "https://github.com/linq2db/linq2db/issues/365"
};
throw ex;
}
}
var parameters = context.Builder.CurrentSqlParameters
.Select((p, i) => new { p, i })
.ToDictionary(_ => _.p.Expression, _ => _.i);
var paramArray = Expression.Parameter(typeof(object[]), "ps");
var groupExpression = context._sequenceExpr.Transform(e =>
{
int idx;
if (parameters.TryGetValue(e, out idx))
{
return
(Expression.Convert(
Expression.ArrayIndex(paramArray, Expression.Constant(idx)),
e.Type));
}
return(e);
});
var keyParam = Expression.Parameter(typeof(TKey), "key");
// ReSharper disable AssignNullToNotNullAttribute
var expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Where(null, (Expression <Func <TSource, bool> >)null)),
groupExpression,
Expression.Lambda <Func <TSource, bool> >(
ExpressionBuilder.Equal(context.Builder.MappingSchema, context._key.Lambda.Body, keyParam),
new[] { context._key.Lambda.Parameters[0] }));
expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Select(null, (Expression <Func <TSource, TElement> >)null)),
expr,
context._element.Lambda);
// ReSharper restore AssignNullToNotNullAttribute
var lambda = Expression.Lambda <Func <IDataContext, TKey, object[], IQueryable <TElement> > >(
Expression.Convert(expr, typeof(IQueryable <TElement>)),
Expression.Parameter(typeof(IDataContext), "ctx"),
keyParam,
paramArray);
var itemReader = CompiledQuery.Compile(lambda);
var keyExpr = context._key.BuildExpression(null, 0, false);
var dataReaderLocal = context.Builder.DataReaderLocal;
if (!Configuration.AvoidSpecificDataProviderAPI && keyExpr.Find(e => e == dataReaderLocal) != null)
{
keyExpr = Expression.Block(
new[] { context.Builder.DataReaderLocal },
new[]
{
Expression.Assign(dataReaderLocal, Expression.Convert(ExpressionBuilder.DataReaderParam, context.Builder.DataContext.DataReaderType)),
keyExpr
});
}
var keyReader = Expression.Lambda <Func <IQueryRunner, IDataContext, IDataReader, Expression, object[], TKey> >(
keyExpr,
new []
{
ExpressionBuilder.QueryRunnerParam,
ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam,
ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam
});
return(Expression.Call(
null,
MemberHelper.MethodOf(() => GetGrouping(null, null, null, null, null, null, null, null)),
new Expression[]
{
ExpressionBuilder.QueryRunnerParam,
ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam,
Expression.Constant(context.Builder.CurrentSqlParameters),
ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam,
Expression.Constant(keyReader.Compile()),
Expression.Constant(itemReader)
}));
}
private static TResult GetValueWithHistory2 <TTableType, TKey, TResult>(
DataContext dataContext,
TKey value,
Expression <Func <TTableType, TKey, bool> > whereExp,
Expression <Func <TTableType, TResult> > selectExp,
bool useCacheValue,
out string keyQuery,
out string keyResult,
params ExpressionCulture <Func <TTableType, TResult> >[] selectExpForCultures)
where TTableType : class, IFilterPeriodHistory2
{
var currentCulture = Thread.CurrentThread.CurrentUICulture.Name;
selectExp = selectExpForCultures
.Where(r => r.CultureName.Equals(currentCulture, StringComparison.OrdinalIgnoreCase))
.Select(r => r.Expression)
.FirstOrDefault() ?? selectExp;
keyQuery = dataContext.GetType().FullName
+ "#" + typeof(TTableType).FullName
+ "#" + typeof(TResult).FullName
+ "#" + typeof(TKey).FullName
+ "#" + whereExp
+ "#" + selectExp
+ "#WithPeriodHistory2";
keyResult = keyQuery + "#" + value;
if (value == null)
{
return(default(TResult));
}
Func <DataContext, TKey, DateTime, TResult> func;
if (HttpContext.Current != null)
{
if (useCacheValue)
{
var obj = HttpContext.Current.Cache[keyResult];
if (obj == DBNull.Value)
{
return(default(TResult));
}
if (obj != null)
{
return((TResult)obj);
}
}
func = (Func <DataContext, TKey, DateTime, TResult>)HttpContext.Current.Cache[keyQuery];
if (func != null)
{
return(func(dataContext, value, DateTime.Now));
}
}
var paramT = Expression.Parameter(typeof(TTableType), "tnc");
var paramKey = Expression.Parameter(typeof(TKey), "tnk");
var exp = Expression.Invoke(whereExp, paramT, paramKey);
var whereT = Expression.Lambda <Func <TTableType, bool> >(exp, paramT);
Expression <Func <DataContext, DateTime, IQueryable <TTableType> > > getTable =
(dc, datetime) =>
dc.GetTable <TTableType>().Where(
r => r.DateStart < datetime && (r.DateEnd == null || r.DateEnd >= datetime));
var paramDc = Expression.Parameter(typeof(DataContext), "dcnc");
var paramDateTime = Expression.Parameter(typeof(DateTime), "datetime");
Expression resultExp = Expression.Invoke(getTable, paramDc, paramDateTime);
resultExp = Expression.Call(typeof(Queryable), "Where", new[] { typeof(TTableType) }, resultExp, whereT);
resultExp = Expression.Call(
typeof(Queryable), "Select", new[] { typeof(TTableType), typeof(TResult) }, resultExp, selectExp);
resultExp = Expression.Call(typeof(Queryable), "FirstOrDefault", new[] { typeof(TResult) }, resultExp);
var resultLambda = Expression.Lambda <Func <DataContext, TKey, DateTime, TResult> >(resultExp, paramDc, paramKey, paramDateTime);
func = CompiledQuery.Compile(resultLambda);
var result = func(dataContext, value, DateTime.Now);
if (HttpContext.Current != null)
{
HttpContext.Current.Cache[keyQuery] = func;
}
return(result);
}
public Expression GetGrouping(GroupByContext context)
{
var parameters = context.Builder.CurrentSqlParameters
.Select((p, i) => new { p, i })
.ToDictionary(_ => _.p.Expression, _ => _.i);
var paramArray = Expression.Parameter(typeof(object[]), "ps");
var groupExpression = context._sequenceExpr.Convert(e =>
{
int idx;
if (parameters.TryGetValue(e, out idx))
{
return
(Expression.Convert(
Expression.ArrayIndex(paramArray, Expression.Constant(idx)),
e.Type));
}
return(e);
});
var keyParam = Expression.Parameter(typeof(TKey), "key");
// ReSharper disable AssignNullToNotNullAttribute
var expr = Expression.Call(
null,
ReflectionHelper.Expressor <object> .MethodExpressor(_ => Queryable.Where(null, (Expression <Func <TSource, bool> >)null)),
groupExpression,
Expression.Lambda <Func <TSource, bool> >(
Expression.Equal(context._key.Lambda.Body, keyParam),
new[] { context._key.Lambda.Parameters[0] }));
expr = Expression.Call(
null,
ReflectionHelper.Expressor <object> .MethodExpressor(_ => Queryable.Select(null, (Expression <Func <TSource, TElement> >)null)),
expr,
context._element.Lambda);
// ReSharper restore AssignNullToNotNullAttribute
var lambda = Expression.Lambda <Func <IDataContext, TKey, object[], IQueryable <TElement> > >(
Expression.Convert(expr, typeof(IQueryable <TElement>)),
Expression.Parameter(typeof(IDataContext), "ctx"),
keyParam,
paramArray);
var itemReader = CompiledQuery.Compile(lambda);
var keyExpr = context._key.BuildExpression(null, 0);
var keyReader = Expression.Lambda <Func <QueryContext, IDataContext, IDataReader, Expression, object[], TKey> >(
keyExpr,
new []
{
ExpressionBuilder.ContextParam,
ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam,
ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam,
});
return(Expression.Call(
null,
ReflectionHelper.Expressor <object> .MethodExpressor(_ => GetGrouping(null, null, null, null, null, null, null, null)),
new Expression[]
{
ExpressionBuilder.ContextParam,
ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam,
Expression.Constant(context.Builder.CurrentSqlParameters),
ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam,
Expression.Constant(keyReader.Compile()),
Expression.Constant(itemReader),
}));
}
public Expression GetGrouping(GroupByContext context)
{
var parameters = context.Builder.CurrentSqlParameters
.Select((p, i) => new { p, i })
.ToDictionary(_ => _.p.Expression, _ => _.i);
var paramArray = Expression.Parameter(typeof(object[]), "ps");
var groupExpression = context._sequenceExpr.Transform(e =>
{
int idx;
if (parameters.TryGetValue(e, out idx))
{
return
(Expression.Convert(
Expression.ArrayIndex(paramArray, Expression.Constant(idx)),
e.Type));
}
return(e);
});
var keyParam = Expression.Parameter(typeof(TKey), "key");
// ReSharper disable AssignNullToNotNullAttribute
var expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Where(null, (Expression <Func <TSource, bool> >)null)),
groupExpression,
Expression.Lambda <Func <TSource, bool> >(
Expression.Equal(context._key.Lambda.Body, keyParam), context._key.Lambda.Parameters[0]));
expr = Expression.Call(
null,
MemberHelper.MethodOf(() => Queryable.Select(null, (Expression <Func <TSource, TElement> >)null)),
expr,
context._element.Lambda);
// ReSharper restore AssignNullToNotNullAttribute
var lambda = Expression.Lambda <Func <IDataContext, TKey, object[], IQueryable <TElement> > >(
Expression.Convert(expr, typeof(IQueryable <TElement>)),
Expression.Parameter(typeof(IDataContext), "ctx"),
keyParam,
paramArray);
var itemReader = CompiledQuery.Compile(lambda);
var keyExpr = context._key.BuildExpression(null, 0);
var dataReaderLocal = context.Builder.DataReaderLocal;
if (!Common.Configuration.AvoidSpecificDataProviderAPI &&
keyExpr.Find(e => e == dataReaderLocal) != null)
{
keyExpr = Expression.Block(
new[] { context.Builder.DataReaderLocal },
Expression.Assign(dataReaderLocal,
Expression.Convert(ExpressionBuilder.DataReaderParam,
context.Builder.DataContextInfo.DataContext.DataReaderType)), keyExpr);
}
var keyReader = Expression
.Lambda <Func <QueryContext, IDataContext, IDataReader, Expression, object[], TKey> >(
keyExpr, ExpressionBuilder.ContextParam, ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam, ExpressionBuilder.ExpressionParam,
ExpressionBuilder.ParametersParam);
return(Expression.Call(
null,
MemberHelper.MethodOf(() => GetGrouping(null, null, null, null, null, null, null, null)),
ExpressionBuilder.ContextParam, ExpressionBuilder.DataContextParam,
ExpressionBuilder.DataReaderParam, Expression.Constant(context.Builder.CurrentSqlParameters),
ExpressionBuilder.ExpressionParam, ExpressionBuilder.ParametersParam,
Expression.Constant(keyReader.Compile()), Expression.Constant(itemReader)));
}
public Expression GetSubquery(
ExpressionBuilder builder,
AssociatedTableContext tableContext,
ParameterExpression parentObject)
{
var lContext = Expression.Parameter(typeof(IDataContext), "ctx");
var lParent = Expression.Parameter(typeof(object), "parentObject");
Expression expression;
var queryMethod = tableContext.Association.GetQueryMethod(parentObject.Type, typeof(T));
if (queryMethod != null)
{
var ownerParam = queryMethod.Parameters[0];
var dcParam = queryMethod.Parameters[1];
var ownerExpr = Expression.Convert(lParent, parentObject.Type);
expression = queryMethod.Body.Transform(e =>
e == ownerParam ? ownerExpr : (e == dcParam ? lContext : e));
}
else
{
var tableExpression = builder.DataContext.GetTable <T>();
var loadWith = tableContext.GetLoadWith();
if (loadWith != null)
{
foreach (var members in loadWith)
{
var pLoadWith = Expression.Parameter(typeof(T), "t");
var isPrevList = false;
Expression obj = pLoadWith;
foreach (var member in members)
{
if (isPrevList)
{
obj = new GetItemExpression(obj);
}
obj = Expression.MakeMemberAccess(obj, member);
isPrevList = typeof(IEnumerable).IsSameOrParentOf(obj.Type);
}
tableExpression = tableExpression.LoadWith(Expression.Lambda <Func <T, object> >(obj, pLoadWith));
}
}
// Where
var pWhere = Expression.Parameter(typeof(T), "t");
Expression expr = null;
for (var i = 0; i < tableContext.Association.ThisKey.Length; i++)
{
var thisProp = Expression.PropertyOrField(Expression.Convert(lParent, parentObject.Type), tableContext.Association.ThisKey[i]);
var otherProp = Expression.PropertyOrField(pWhere, tableContext.Association.OtherKey[i]);
var ex = ExpressionBuilder.Equal(tableContext.Builder.MappingSchema, otherProp, thisProp);
expr = expr == null ? ex : Expression.AndAlso(expr, ex);
}
var predicate = tableContext.Association.GetPredicate(parentObject.Type, typeof(T));
if (predicate != null)
{
var ownerParam = predicate.Parameters[0];
var childParam = predicate.Parameters[1];
var ownerExpr = Expression.Convert(lParent, parentObject.Type);
var body = predicate.Body.Transform(e =>
e == ownerParam ? ownerExpr : (e == childParam ? pWhere : e));
expr = expr == null ? body : Expression.AndAlso(expr, body);
}
expression = tableExpression.Where(Expression.Lambda <Func <T, bool> >(expr, pWhere)).Expression;
}
var lambda = Expression.Lambda <Func <IDataContext, object, IEnumerable <T> > >(expression, lContext, lParent);
var queryReader = CompiledQuery.Compile(lambda);
expression = Expression.Call(
null,
MemberHelper.MethodOf(() => ExecuteSubQuery(null, null, null)),
ExpressionBuilder.QueryRunnerParam,
Expression.Convert(parentObject, typeof(object)),
Expression.Constant(queryReader));
var memberType = tableContext.Association.MemberInfo.GetMemberType();
if (memberType == typeof(T[]))
{
return(Expression.Call(null, MemberHelper.MethodOf(() => Enumerable.ToArray <T>(null)), expression));
}
if (memberType.IsSameOrParentOf(typeof(List <T>)))
{
return(Expression.Call(null, MemberHelper.MethodOf(() => Enumerable.ToList <T>(null)), expression));
}
var ctor = memberType.GetConstructorEx(new[] { typeof(IEnumerable <T>) });
if (ctor != null)
{
return(Expression.New(ctor, expression));
}
var l = builder.MappingSchema.GetConvertExpression(expression.Type, memberType, false, false);
if (l != null)
{
return(l.GetBody(expression));
}
throw new LinqToDBException($"Expected constructor '{memberType.Name}(IEnumerable<{tableContext.ObjectType}>)'");
}
