Converting Entity to DTO along with child entities

Question

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!

Answer 1

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 :

• https://www.youtube.com/watch?v=emgJtr9tIME
• https://www.reddit.com/r/csharp/comments/40x3nx/which_orm_is_the_right_choice_for_mysql

Answer 2

An include should do the trick for you like so:

    IEnumerable<SystemArea> result = (from sa in CurrentContext.systemarea.Include("systemareafunctionality")                                 
                             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();

Answer 3

Apparently your CurrentContext is a Dbcontext with at least a table of SystemAreas and a table of SystemAreaFunctionalities.

It seems that every SystemArea has zero or more SystemAreaFunctionalities; every SystemAreaFunctionality belongs to exactly one SystemArea, a straightforward one-to-many relationship using a foreign key.

Note: it might be that you have a many-to-many relation, the answer will be similar

Alas you forgot to write your classes, so I'll give a shot:

class SystemArea
{
    public int Id {get; set;}
    ... // other properties

    // every SystemArea has zero or more SystemAreaFunctionalities (one-to-many)
    public virtual ICollection<SystemAreaFunctionality> SystemAreaFunctionalities {get; set;}
}

class SystemAreaFunctionality
{
    public int Id {get; set;}
    ... // other properties

    // every SystemAreaFunctionality belongs to exactly one SystemArea, using foreign key
    public int SystemAreaId {get; set;}
    public virtual SystemArea SystemArea {get; set;}
}

In entity framework the columns of your tables are represented by non-virtual properties, the virtual properties represent the relationships between the tables. (one-to-many, many-to-many, ...)

for completeness:

class CurrentContext : DbContext
{
    public DbSet<SystemArea> SystemAreas {get; set;}
    public DbSet<SystemAreaFunctionality> SystemAreaFunctionalities {get; set;}
}

If people want items with their sub-items, like Schools with their Students, Customers with their Orders, etc. people tend to perform a (group)Join. However, when using entity framework joins are seldom necessary. Ise the ICollections instead. Entity framework knows the relationships and knows which (group)join to perform.

Regularly I see people use Include, but if you do that, you'll select the complete object, which is not very efficient. Suppose you have a SystemArea with Id = 10, and 1000 SystemAreaFunctionalities, you know that every SystemAreaFunctionality has a foreign key SystemAreaId with a value 10. Instead of sending this value only once as primary key of SystemArea, Include will also select all 1000 foreign keys with this value 10. What a waste of processing power!

When querying data, always use Select and select only the properties you actually plan to use. Only use Include if you plan to Update the included object.

You wrote:

SystemAreaFunctionality is the child object that i am trying to convert here...

It is not clear what you really want. Do you want a collection of all used SystemAreaCodes? Or do you really want a collection of new SystemAreaFunctionalities where only one field is filled: SystemAreaCode? Because of you use of singular property name, it seems you don't want a collection but only one item.

var result = currentContext.SystemAreas.Select(systemArea => new
{
     Id = systemArea.Id,
     Code = systemArea.Code,
     ...

     // if you want a collection of SystemAreaFunctionalities
     // where every SystemAreaFunctionality is filled with only SysemAreaCode
     // do the following:
     SystemAreaFunctionalities = systemArea.SystemAreaFunctionalities
          .Select(systemAreaFunctionality => new SystemAreFunctionality
          {
               SystemAreaCode = systemAreaFunctionality.SystemAreaCode,
          })
          .ToList(),   // DON'T FORGET THIS, OR YOU WON'T GET RESULTS!
    })
    .ToList()
}

I think the cause of your empty SystemAreaFunctionalities is because you forgot to do ToList().

Because you used ToList(), you automatically have the Count of the selected SystemAreaFunctionalities. There is no need to select this Count separately.

One of the slower parts of database queries is the transport of the selected data from the database management system to your local process. It is good practice to only select data you actually plan to use

You query is not very efficient because you select complete SystemAreaFunctionalities and fill only the SystemAreaCode. All other fields will be filled by default values. Apart from the slower query, you also give your callers the impression that they get properly filled SystemAreaFunctionalities. An improved version would be:

var result = currentContext.SystemAreas.Select(systemArea => new
{
     // select only the properties you actually plan to use
     Id = systemArea.Id,
     Code = systemArea.Code,
     ...


     // if you only need the SystemAreaCodes, select only that property
     SystemAreaCodes = systemArea.SystemAreaFunctionalities
          .Select(systemAreaFunctionality => systemAreaFunctionality.SystemAreaCode)
          .ToList(),
    })
    .ToList()
};

Of courds, if you need mrre than only the SystemAreaCodes, but several SystemAreaFunctionalities, go ahead, select them:

...
SystemAreaFunctionalities = systemArea.SystemAreaFunctionalities
    .Select(systemAreaFunctionality => new
    {
        // again: select only the properties you plan to use!
        Id = systemAreaFunctionality.Id
        SystemAreaCode = systemAreaFunctionality.SystemAreaCode,
     })

Answer 4

Use this:

CurrentContext.systemarea.Include('systemareafunctionality')

or

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

or

IEnumerable<SystemArea> result = (from sa in CurrentContext.systemarea    
                         join systemareafunctionality in CurrentContext.systemareafunctionality on sa.systemareafunctionalityID equals systemareafunctionality.ID into item1 from subitem1 in item1.DefaultIfEmpty() 
                         select new SystemArea
                         {
                             SystemAreaId = sa.SystemAreaId,
                             SystemAreaCode = sa.SystemAreaCode,
                             SystemAreaType = sa.SystemAreaType,
                             SystemAreaDescription = sa.SystemAreaDescription,
                             SystemAreaCreatedDate = sa.SystemAreaCreatedDate,
                             SystemAreaUpdateDate = sa.SystemAreaUpdateDate,
                             SystemAreaStatus = SystemAreaStatus,
                             Count = systemareafunctionality.Count,
                             SystemAreaFunctionality = systemareafunctionality.Select(e => new SystemAreaFunctionality { SystemAreaCode =e.SystemAreaCode })
                         }).ToList();