Solution to compare ORM performances. NPOCO, EFCore,DAPPER and ADODBNET The test example it's only to identify which ORM is the best solution to use in .net Core2. This solution uses the "BenchMarkDonetNet V(0.10.14)" tool.
With this test I try to use query with some complexity, join, filters and orders. eg:
- Join 2 Tables
- Order by Foreing Key
- Filter by the join field id
- Top 100
The is the result for each query mady by the ORM, to prove the same sql query results .
SqlDataAdapter("SELECT Top 1000 CLH.Id, CLH.Description, CLH.CreatedDateTime, CLH.UpdatedDateTime,
CLH.IsObsolete, CLH.ObsoletedDateTime, CLH.CheckTypeId, CLH.IsSystem, CLH.VehicleTypeId,
CT.Id AS CT_Id,CT.CreatedDateTime AS CT_CreatedDateTime, CT.UpdatedDateTime AS CT_UpdatedDateTime,
CT.Name AS CT_Name, CT.IsSystem AS CT_IsSystem
from CheckListHeader CLH
left join CheckType CT on CLH.CheckTypeId=Ct.Id
where CheckTypeId=@ID order by VehicleTypeId asc",conn)exec sp_executesql N'SELECT Top 100 CLH.Id, CLH.Description, CLH.CreatedDateTime,
CLH.UpdatedDateTime, CLH.IsObsolete, CLH.ObsoletedDateTime, CLH.CheckTypeId, CLH.IsSystem,
CLH.VehicleTypeId, CT.Id AS CT_Id, CT.CreatedDateTime AS CT_CreatedDateTime,
CT.UpdatedDateTime AS CT_UpdatedDateTime,CT.Name AS CT_Name, CT.IsSystem AS CT_IsSystem
from CheckListHeader CLH left join CheckType CT on CLH.CheckTypeId=Ct.Id
where CheckTypeId=@ID
order by VehicleTypeId asc',N'@ID int',@ID=1Dapper V(1.50.4)
db.Query<CheckListHeaderADODB>(
$"SELECT Top 1000 CLH.Id, CLH.Description, CLH.CreatedDateTime, CLH.UpdatedDateTime,
CLH.IsObsolete, CLH.ObsoletedDateTime, CLH.CheckTypeId, CLH.IsSystem, CLH.VehicleTypeId,
CT.Id AS CT_Id,CT.CreatedDateTime AS CT_CreatedDateTime, CT.UpdatedDateTime AS CT_UpdatedDateTime,
CT.Name AS CT_Name, CT.IsSystem AS CT_IsSystem
from CheckListHeader CLH
left join CheckType CT on CLH.CheckTypeId=Ct.Id
where CheckTypeId={checkTypeId} order by VehicleTypeId asc")
.ToList()SELECT Top 100 CLH.Id, CLH.Description, CLH.CreatedDateTime, CLH.UpdatedDateTime,
CLH.IsObsolete, CLH.ObsoletedDateTime,CLH.CheckTypeId, CLH.IsSystem,
CLH.VehicleTypeId, CT.Id AS CT_Id, CT.CreatedDateTime AS CT_CreatedDateTime,
CT.UpdatedDateTime AS CT_UpdatedDateTime, CT.Name AS CT_Name, CT.IsSystem AS CT_IsSystem
from CheckListHeader CLH left join CheckType CT on CLH.CheckTypeId=Ct.Id
where CheckTypeId=1 order by VehicleTypeId ascdb.CheckListHeader
.Where(b => b.CheckTypeId == CheckTypeId)
.OrderBy(b => b.VehicleTypeId)
.Include(p => p.CheckType).Take(10);exec sp_executesql N'SELECT TOP(@__p_1) [b].[Id], [b].[CheckTypeId], [b].[CreatedDateTime],
[b].[Description], [b].[IsObsolete], [b].[IsSystem], [b].[UpdatedDateTime], [b].[VehicleTypeId],
[b.CheckType].[Id], [b.CheckType].[CreatedDateTime], [b.CheckType].[IsSystem],
[b.CheckType].[Name], [b.CheckType].[UpdatedDateTime]
FROM [CheckListHeader] AS [b]
INNER JOIN [CheckType] AS [b.CheckType] ON [b].[CheckTypeId] = [b.CheckType].[Id]
WHERE [b].[CheckTypeId] = @__CheckTypeId_0
ORDER BY [b].[VehicleTypeId]',N'@__p_1 int,@__CheckTypeId_0 int',@__p_1=100,@__CheckTypeId_0=1NPOCO V(3.9.3)
db.Query<CheckListHeader>()
.Include(m => m.CheckType)
.Where(m => m.CheckTypeId == CheckTypeId)
.OrderBy(m => m.VehicleTypeId)
.Limit(10);exec sp_executesql N'SELECT [CLH].[Description] as [Description], [CLH].[IsObsolete] as [IsObsolete],
[CLH].[IsSystem] as [IsSystem], [CLH].[CheckTypeId] as [CheckTypeId],
[CLH].[VehicleTypeId] as [VehicleTypeId], [CLH].[Id] as [Id], [CLH].[CreatedDateTime] as [CreatedDateTime],
[CLH].[UpdatedDateTime] as [UpdatedDateTime], [CT].[Name] as [CheckType__Name],
[CT].[IsSystem] as [CheckType__IsSystem], [CT].[Id] as [CheckType__Id],
[CT].[CreatedDateTime] as [CheckType__CreatedDateTime],
[CT].[UpdatedDateTime] as [CheckType__UpdatedDateTime] FROM [CheckListHeader] [CLH]
LEFT JOIN [CheckType] [CT] ON [CLH].[CheckTypeId] = [CT].[Id]
WHERE ([CLH].[CheckTypeId] = @0)
ORDER BY [VehicleTypeId] ASCIf the project is small with small amount of data, I suggest to use entity framework, but if the project can get a large amount of data It’s very clear NPOCO we should use have the best test performance for more records results, at this moment is very easy to use and it 's possible to have the same functionalities like we have with entity framework, “includes,joins”, exchange data directly with the object using LINQ and for me the most relevant SQL Transaction Support.