[:de].NET: LINQ SQL-Queries mit AutoMapper optimieren[:en].NET: Optimizing LINQ SQL queries with AutoMapper[:]

Last Updated on 2018-12-06.

[:en]AutoMapper is not only a useful tool for mapping e.g. DB model classes to flatter ViewModels, it can also simplify and optimize SQL queries / expressions generated by LINQ.

You can get it via NuGet for any .NET framework type.

Example

Let’s suppose we have a simple DB table User. Each user belongs to a UserGroup and owns several items, stored in UserItem. The tables are “connected” via common IDs and SQL foreign keys. The DB classes were generated via common tools like “dotnet ef scaffold”.

We want to get an overview of the users, including a property of the parent (UserGroup.Name) and a sum of the user’s items. The table is huge, so the whole preparation and calculation should be done by the server to reduce network traffic.

The data will be stored in a flat ViewModel class UserView, which contains the same structure as User, but with 2 additional fields, UserGroupName and UserItemSum.

First we prepare AutoMapper:

//using AutoMapper;
Mapper.Initialize(cfg =>
{
    cfg.CreateMap<User, UserView>()
      .ForMember(uview => uview.UserItemSum, opt => opt.MapFrom(user => user.UserItem.Sum(item => item.Number)))
});

Create the context instance for DB access:

var db = new mydbContext();

var dto = db.User.ProjectTo<UserView>().ToList();

//example for proceeding
myBindingSourceForDataGrid.DataSource = dto;

…and that was about it. We do not need any more Include() of subtables, Where(), Select() or anything else, and the whole query is run on the server side (the SQL server).

AutoMapper is smart enough to recognize that the UserView.UserGroupName column should belong to the user’s UserGroup.Name property.

In CreateMap(), we just need to tell how it handles the UserItemSum column.

Taking a look at the SQL query log, we see that the query in fact contains only the needed data:

SELECT `User`.`Fullname`, `User.UserGroup`.`Name` AS `UserGroupName`, `User`.`id`, (
    SELECT SUM(`item`.`Number`)
    FROM `mydb`.`UserItem` AS `item`
    WHERE `User`.`id` = `item`.`userId`
) AS `UserItemSum`
FROM `mydb`.`User` AS `User`
INNER JOIN `mydb`.`UserGroup` AS `User.UserGroup` ON `User`.`UserGroupid` = `User.UserGroup`.`id`

 

 

References

http://docs.automapper.org/en/stable/Queryable-Extensions.html

 

 [:]