How can I convert Linq results to DTO class object without iteration

Question

I'm building a Web API project that will be made available to third-party's and also used by my own web application/s. The Web API methods will return JSON representations of complex types/objects. These are pre-defined classes which I can make available to third-party's so they understand how the data is structured and can deserialize the JSON. I'll refer to these classes as DTO classes until someone corrects me.

I have the following auto-generated entity model (from database) well this is the User class anyway with a relationship to the Scan table (relationship can be ignored for the purpose of this question)...

public partial class User
{
    public User()
    {
        this.Scans = new HashSet<Scan>();
    }

    public int Id { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public bool Active { get; set; }

    public virtual ICollection<Scan> Scans { get; set; }
}

And the following DTO class which I would like to return to the presentation layer as List (I don't think I should use IEnumerable for business to presentation?).

public class User
{
    public int Id;
    public string Username;
    public string Password;
    public bool Active;
}

My business layer for this component currently looks like below. Using Linq to get users from the database and then parsing the results and returning a List<> of POCO Users.

public List<User> Get()
{
    List<User> userList = new List<User>();

    using (var db = new MyContext())
    {
        var query = from u in db.Users
                    orderby u.FirstName
                    select u;

        foreach (var item in query)
        {
            User user = new User();
            user.Id = item.pkUser;
            user.Username = item.Username;
            user.Password = item.Password;
            user.Active = item.Active;

            userList.Add(user);
        }
    }

    return userList;
}

Parsing through the results like this seems inefficient, and I have seen that you can do this sort of thing in the Linq query without iteration (e.g. the answer in this question Mapping Linq Query results to a DTO class).

I'm unsure as to where/how I should implement IEnumerable to enable this type of Linq query on my objects, and am also unsure of how to write a query similar to the one in the referenced question above but for my much simpler scenario.

Thanks in advance for any help.

P.S. Please ignore the fact that I am exposing Username and Password via Web API and also that I am returning all Users at once. Above is a simplified version of my code for the purpose of this question.

You're gonna iterate no matter what. You can use `Enumerable.Select` in order to transform your list. — Dustin Kingen, Feb 27 '13 at 23:58

Mohammad Dehghan · Accepted Answer · 2013-02-28T00:07:18.000

39

The complete method could be:

public List<User> Get()
{
    using (var db = new MyContext())
    {
        return (from u in db.Users
                orderby u.FirstName
                select new User()
                {
                    Id = u.pkUser,
                    Username = u.Username,
                    Password = u.Password,
                    Active = u.Active
                }).ToList();
    }
}

You said you want the result "without iteration". Using LINQ also does not eliminate the iteration. You are not doing it in your code, but it really happens when you call the ToList() method.

edited Feb 28 '13 at 00:07

answered Feb 28 '13 at 00:00

Mohammad Dehghan

17,853
3
55
72

I'm going to go first in best dressed with the simplest and most flexible answer. Thanks. – Dan Feb 28 '13 at 00:15
@Mohammad Dehghan Does the iteration happen on the SQL server? Or within the Linq library code on your client? – Pangamma Feb 07 '19 at 19:05
@Pangamma The `ToList()` method call performs an iteration on the result retrieved from SQL Server. So the iteration happens in the client code, in `ToList()` method. SQL Server (or any other database engine) has it's own mechanism for sorting/filtering/retrieving data, that may contain iterations/tree traversal/key computations/etc. This usually is not the concern of any application using that database engine. – Mohammad Dehghan Feb 08 '19 at 10:03

score 9 · Answer 2 · answered Feb 27 '13 at 23:58

You can use LINQ to Objects to do the conversion:

using (var db = new MyContext())
{
    var query = from u in db.Users
                orderby u.FirstName
                select u;

    return query.AsEnumerable().Select(item => 
                 new User 
                     { 
                        Id = item.pkUser,
                        Username = item.Username,
                        Password = item.Password,
                        Active = item.Active
                     }).ToList();
}

score 2 · Answer 3 · answered Feb 28 '13 at 00:07

2

We can get really compact if we use AutoMapper.

Bootstrap the mapper in your repository:

Mapper.CreateMap<AutoGenNamespace.User, DtoNamespace.User>();

Then it's pretty simple (and there's nothing wrong with returning IEnumerable).

public IEnumerable<User> Get()
{
    using (var db = new MyContext())
    {
        return (from u in db.Users
                orderby u.FirstName
                select Mapper.Map(u)).AsEnumerable();
    }
}

answered Feb 28 '13 at 00:07

Dustin Kingen

20,677
7
52
92

I don't agree with "*nothing wrong*". Something *could* go wrong! `var list = Get();` and then iterating over the list more than once. – Mohammad Dehghan Feb 28 '13 at 00:10
@MD.Unicorn well then `var list = Get().ToList();` solves that just fine. You know you're getting an enumerable back from the method by the signature. – Dustin Kingen Feb 28 '13 at 00:13
Yes I know! But are you sure that Dan knows?! :-) – Mohammad Dehghan Feb 28 '13 at 00:14
@MD.Unicorn well he should know now. – Dustin Kingen Feb 28 '13 at 00:15
Thanks for pointing this out Romoko, it looks really useful (and simple) but contrary to my question, the class properties are not actually the same (as you all probably noticed in the last code block, the database uses pk/fk prefixes on key fields). – Dan Feb 28 '13 at 00:18
@Dan You're not limited in how you define your mappings with `AutoMapper`. I use it for my data layer and you have 100% control over how your objects get mapped. The best part is once your mappings are defined then you can reuse them. – Dustin Kingen Feb 28 '13 at 00:51

score -1 · Answer 4 · answered Jan 13 '16 at 00:57

In my query I have parent table with multiple childs.

public class TeamWithMembers
        {
            public int TeamId { get; set; }
            public string TeamName { get; set; }
            public string TeamDescription { get; set; }
            public List<TeamMembers> TeamMembers { get; set; }
        }

In this when I was using the ToList() in the linq query it is giving the error. Now I am using the anonymous type in the linq query and convert it into the next query like this

List<TeamModel.TeamWithMembers> teamMemberList = teamMemberQueryResult.AsEnumerable().Select(item =>
new TeamModel.TeamWithMembers() {
    TeamId=item.TeamId,
    TeamName=item.TeamName,
    TeamMembers=item.TeamMembers.ToList()
}).ToList();

Where teamMemberQueryResult is the resultset from the linq query.

How can I convert Linq results to DTO class object without iteration

4 Answers4

Linked