locked
Working With Union and Data Transfer Object In EF RRS feed

  • Question

  • Hello guys,

    Please, i need help on how to work with Data transfer object class and Union. Most especially with union, haven't worked with it.

    The scenario is this.

    I have two tables where i need to get some specific information. Each table are different but somehow have some related columns i need to pull out to the view. I could handle this using one Dto per table....but what if i have multiple table and i need to take out some info....How do i structure my query. An example is below;

    var result = new List<Data>(); using (Model db = new Model()) { result = ( from b in db.bills where b.user_id == u.user_id select new Data{

    //data i need

    }

    ).Union( from c in db.consumables where c.user_id == u.user_id select new Data{

    //data i need

    } ); }


    This information i need is based on a specific userID passed....

    Bear in mind that, as my table grows, i intend to add more unions to other tables and pull out information. But for now, i just have 3 tables i wana work with.

    Any idea how to structure my query??. 

    Appreciate any feedback.

    But as the table grows, i want to be able to add more unions to table to get information i need. 
    Monday, December 3, 2018 6:24 AM

Answers

  • You have common properties that you are pulling from various tables/objects from the model,  then you should be doing a Linq projection into a DTO, a DTO per table, that would be projected into a collection of DTO(s) per table.

    Below is code that does a Linq projection into a collection DtoProject.

    So after you do your queries to make the collection of DTO(s) that have like properties in two collections, then you can do a Union on the two collections.

    https://csharp-station.com/Tutorial/Linq/Lesson02

    public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }

    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }

    Hi DA924x,

    Appreciate your feedback.

    I am familiar with the projection code you showed above. Its just working with Union i am not familiar with.  Besides there are some columns that are not in other table as well that i need to pull out.

    Do you have similar code where you used Union for two tables and Dto to carry the data?. I just need to see your syntax and structure to have a better understanding if possible. 

    Appreciate your feedback.

    You union on the collection of like objects in the List<T>(s) that is created by the Linq queries.  You don't union during the Linq query.

    http://www.alexyork.net/blog/2009/06/15/performing-a-linq-union-on-two-anonymous-type-collections-with-c/

    • Marked as answer by Godymn Tuesday, December 4, 2018 3:49 AM
    Monday, December 3, 2018 4:24 PM

All replies

  • You have common properties that you are pulling from various tables/objects from the model,  then you should be doing a Linq projection into a DTO, a DTO per table, that would be projected into a collection of DTO(s) per table.

    Below is code that does a Linq projection into a collection DtoProject.

    So after you do your queries to make the collection of DTO(s) that have like properties in two collections, then you can do a Union on the two collections.

    https://csharp-station.com/Tutorial/Linq/Lesson02

    public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }

    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }
    

    Monday, December 3, 2018 1:50 PM
  • You have common properties that you are pulling from various tables/objects from the model,  then you should be doing a Linq projection into a DTO, a DTO per table, that would be projected into a collection of DTO(s) per table.

    Below is code that does a Linq projection into a collection DtoProject.

    So after you do your queries to make the collection of DTO(s) that have like properties in two collections, then you can do a Union on the two collections.

    https://csharp-station.com/Tutorial/Linq/Lesson02

    public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }

    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }

    Hi DA924x,

    Appreciate your feedback.

    I am familiar with the projection code you showed above. Its just working with Union i am not familiar with.  Besides there are some columns that are not in other table as well that i need to pull out.

    Do you have similar code where you used Union for two tables and Dto to carry the data?. I just need to see your syntax and structure to have a better understanding if possible. 

    Appreciate your feedback.

    Monday, December 3, 2018 2:19 PM
  • You have common properties that you are pulling from various tables/objects from the model,  then you should be doing a Linq projection into a DTO, a DTO per table, that would be projected into a collection of DTO(s) per table.

    Below is code that does a Linq projection into a collection DtoProject.

    So after you do your queries to make the collection of DTO(s) that have like properties in two collections, then you can do a Union on the two collections.

    https://csharp-station.com/Tutorial/Linq/Lesson02

    public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }

    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }

    Hi DA924x,

    Appreciate your feedback.

    I am familiar with the projection code you showed above. Its just working with Union i am not familiar with.  Besides there are some columns that are not in other table as well that i need to pull out.

    Do you have similar code where you used Union for two tables and Dto to carry the data?. I just need to see your syntax and structure to have a better understanding if possible. 

    Appreciate your feedback.

    You union on the collection of like objects in the List<T>(s) that is created by the Linq queries.  You don't union during the Linq query.

    http://www.alexyork.net/blog/2009/06/15/performing-a-linq-union-on-two-anonymous-type-collections-with-c/

    • Marked as answer by Godymn Tuesday, December 4, 2018 3:49 AM
    Monday, December 3, 2018 4:24 PM
  • Hi Godymn,

    Please add ToList method in your statement. like this:

    var result = new List<Data>();
                using (Model db = new Model())
                {
                    result = (
                        from b in db.bills
                        where b.user_id == u.user_id
                        select new Data{
                        //data i need
                         }
                         ).Union(
                        from c in db.consumables
                        where c.user_id == u.user_id
                        select new Data{
                        //data i need
                        }
                        ).ToList();
                }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 4, 2018 3:33 AM
  • You have common properties that you are pulling from various tables/objects from the model,  then you should be doing a Linq projection into a DTO, a DTO per table, that would be projected into a collection of DTO(s) per table.

    Below is code that does a Linq projection into a collection DtoProject.

    So after you do your queries to make the collection of DTO(s) that have like properties in two collections, then you can do a Union on the two collections.

    https://csharp-station.com/Tutorial/Linq/Lesson02

    public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }

    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }

    Hi DA924x,

    Appreciate your feedback.

    I am familiar with the projection code you showed above. Its just working with Union i am not familiar with.  Besides there are some columns that are not in other table as well that i need to pull out.

    Do you have similar code where you used Union for two tables and Dto to carry the data?. I just need to see your syntax and structure to have a better understanding if possible. 

    Appreciate your feedback.

    You union on the collection of like objects in the List<T>(s) that is created by the Linq queries.  You don't union during the Linq query.

    http://www.alexyork.net/blog/2009/06/15/performing-a-linq-union-on-two-anonymous-type-collections-with-c/

    Hi DA924x,

    Thanks, the link provided was helpful.

    Tuesday, December 4, 2018 3:49 AM
  • Hi Godymn,

    Please add ToList method in your statement. like this:

    var result = new List<Data>();
                using (Model db = new Model())
                {
                    result = (
                        from b in db.bills
                        where b.user_id == u.user_id
                        select new Data{
                        //data i need
                         }
                         ).Union(
                        from c in db.consumables
                        where c.user_id == u.user_id
                        select new Data{
                        //data i need
                        }
                        ).ToList();
                }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Zhanglong,

    The link provided by DA924x was helpful and i have been able to figure something out from it. 

    Thanks for your response.

    Tuesday, December 4, 2018 3:50 AM