c# - Converting Entity to DTO along with child entities

问题描述

Trying to convert an entity object to local object so i can use it for further transformations.

Here is the code that i am using to convert the entity object;

IEnumerable<SystemArea> result = (from sa in CurrentContext.systemarea                                 
                                 select new SystemArea
                                 {
                                     SystemAreaId = sa.SystemAreaId,
                                     SystemAreaCode = sa.SystemAreaCode,
                                     SystemAreaType = sa.SystemAreaType,
                                     SystemAreaDescription = sa.SystemAreaDescription,
                                     SystemAreaCreatedDate = sa.SystemAreaCreatedDate,
                                     SystemAreaUpdateDate = sa.SystemAreaUpdateDate,
                                     SystemAreaStatus = sa.SystemAreaStatus,
                                     Count = sa.systemareafunctionality.Count,
                                     SystemAreaFunctionality = sa.systemareafunctionality.Select(e => new SystemAreaFunctionality { SystemAreaCode =e.SystemAreaCode })
                                 }).ToList();

Here the count variable is to confirm whether there is any child data in it.

SystemAreaFunctionality is the child object that i am trying to convert here by using SELECT function but it is always blank collection. Rest data is getting assigned to parent object but the only thing missing here is the child table records. Where am i going wrong, please help!

Generated SQL :

SELECT
`Project3`.`C1`, 
`Project3`.`SystemAreaId`, 
`Project3`.`SystemAreaCode`, 
`Project3`.`SystemAreaType`, 
`Project3`.`SystemAreaDescription`, 
`Project3`.`SystemAreaCreatedDate`, 
`Project3`.`SystemAreaUpdateDate`, 
`Project3`.`SystemAreaStatus`, 
`Project3`.`C3` AS `C2`, 
`Project3`.`C2` AS `C3`, 
`Project3`.`SystemAreaCode1`
FROM (SELECT
`Project1`.`SystemAreaId`, 
`Project1`.`SystemAreaCode`, 
`Project1`.`SystemAreaType`, 
`Project1`.`SystemAreaDescription`, 
`Project1`.`SystemAreaCreatedDate`, 
`Project1`.`SystemAreaUpdateDate`, 
`Project1`.`SystemAreaStatus`, 
1 AS `C1`, 
`Project2`.`SystemAreaCode` AS `SystemAreaCode1`, 
`Project2`.`C1` AS `C2`, 
`Project1`.`C1` AS `C3`
FROM (SELECT
`Extent1`.`SystemAreaId`, 
`Extent1`.`SystemAreaCode`, 
`Extent1`.`SystemAreaType`, 
`Extent1`.`SystemAreaDescription`, 
`Extent1`.`SystemAreaCreatedDate`, 
`Extent1`.`SystemAreaUpdateDate`, 
`Extent1`.`SystemAreaStatus`, 
(SELECT
COUNT(1) AS `A1`
FROM `systemareafunctionality` AS `Extent2`
 WHERE `Extent1`.`SystemAreaCode` = `Extent2`.`SystemAreaCode`) AS `C1`
FROM `systemarea` AS `Extent1`) AS `Project1` LEFT OUTER JOIN (SELECT
`Extent3`.`SystemAreaCode`, 
1 AS `C1`
FROM `systemareafunctionality` AS `Extent3`) AS `Project2` ON `Project1`.`SystemAreaCode` = `Project2`.`SystemAreaCode`) AS `Project3`
 ORDER BY 
`Project3`.`SystemAreaCode` ASC, 
`Project3`.`C2` ASC

JSON output:

[{"SystemAreaId":1,"SystemAreaCode":"KIO","SystemAreaType":"KIOSK","SystemAreaDescription":"tasks related to receptionist","SystemAreaCreatedDate":"/Date(1543421018000)/","SystemAreaUpdateDate":"/Date(1543421018000)/","SystemAreaStatus":true,"SystemAreaFunctionality":[],"Count":1}]

PS : Please don't suggest automapper or extension methods. Thanks!

标签： c#mysqlentity-frameworklinq

解决方案

OPINION :

Took me two days to make MySQL(latest version) work with EF and trust me it was painstaking and on the contrary EF with MSSQL is so simple and easy to implement.

One thing i experienced is that Oracle is not interested in providing support for the free version of MySQL whatsoever, so they are being sloppy on the documentation of new version and are providing unstable .NET connectors.

ACTUAL ANSWER :

EF was behaving so weirdly, that it would only load the data in the child entity (SystemAreaFunctionality) only if i asked EF to load the child of the child entity (i.e. SystemAreaFunctionalityEmployeeRoleMapping which is child to SystemAreaFuncionality), which also means that i had to take unnecessary data.

So my link query looks like this :

var result = (from sa in CurrentContext.systemarea
                          select new SystemArea
                          {
                              SystemAreaId = sa.SystemAreaId,
                              SystemAreaType = sa.SystemAreaType,
                              Count = sa.systemareafunctionality.Count,
                              SystemAreaFunctionalities = sa.systemareafunctionality.Select(saf => new SystemAreaFunctionality
                              {
                                  SystemAreaId = saf.SystemAreaId,
                                  SystemAreaFunctionalityController = saf.SystemAreaFunctionalityController,
                                  SystemAreaFunctionalityAction = saf.SystemAreaFunctionalityAction,
                                  SystemAreaFunctionalityType = saf.SystemAreaFunctionalityType,
                                  SystemAreaFunctionalityEmployeeRoleMappings = saf.systemareafunctionalityemployeerolemapping.Select(saferm => new SystemAreaFunctionalityEmployeeRoleMapping
                                  {
                                      SystemAreaFunctionalityEmployeeRoleMappingId = saferm.SystemAreaFunctionalityEmployeeRoleMappingId,
                                      SystemAreaFunctionalityCreatedDate = saferm.SystemAreaFunctionalityCreatedDate
                                  })
                              })
                          }).ToList();

ALTERNATIVELY :

Tried using the same linq query (posted in OP) with different database this time with PostgreSQL plus npgsql connector and surprisingly EF gives me exactly what i want with out extra baggage. On top of that PostgreSQL gives better performance with EF than MySQL. So i presume that switching to PostgreSQL would be a better option.

PS : If you are deciding on open sources DBMS then please refer this before jumping in with MySQL :

c# - Converting Entity to DTO along with child entities

问题描述

解决方案

推荐阅读