CODEWITHSUNDEEP
c#visual-studio-2008.net-3.5
Misiu
Misiu

Reputation: 4919

SQL Query Result to List<T> mapping

In my database I have 2 tables:

id   | name |  city  | street
-------------------------------
   1 | John | London | Street1
   2 | Will | London | Street1
   3 | Adam | LA     | Street1

id   | uid | phone
------------------
   1 |    1| 12345
   2 |    1| 23456
   3 |    2| 16505
   4 |    3| 65909
   5 |    3| 68902
   6 |    3| 15905

I need to select that data and store into List (or Dictionary as user Id is unique)

My uses class looks like so:

public class User
{
    //lock
    private static readonly object userLock = new object();

    public int      Id        { get; set; }
    public string   Name      { get; set; }
    public string   City      { get; set; }
    public string   Street    { get; set; }

    public List<string> Phones { get; private set; }

    public User()
    {
        Phones = new List<string>();
    }

    public void AddPhone(string phone)
    {
        lock (userLock)
        {
            Phones.Add(phone);
        }
    }
}

Idea is to load those users info into application, add/remove/modify phones and save those changed/removed/added.

I found idea for solution here: https://stackoverflow.com/a/11687743/965722, but I imagine this as doing one query to get users list and then for each user I need separate query to get his phones.

How should I do my loading?
Can I do it with only one query?
How should I populate my result collection?
Should I use List or Dictionary? Then I could remove Id from User class and have it as key inside dictionary.

I'm using .NET 3.5 and would like to avoid Entity Framework or ORM's.

Upvotes: 2

Views: 5436

Answers (2)

Eamon Nerbonne
Eamon Nerbonne

Reputation: 48066

If you can upgrade to .net 4, then take a look at ObjectContext.Translate - you can use this without a "real" entity model, an empty unsubclassed objectcontext suffices and will map objects even if they're not entities. This solution has no third party dependencies, but it's a little slower and less flexible than the open source libraries available.

If .net 4.0 is not an option or you want more features and performance you may want to look at a third-party lib:

  • Dapper Unwraps plain POCOs nicely and has nice parametrized queries. Also very fast. The newest version is .net 4.0, but there's a 3.5 version too.
  • PetaPoco Another simple POCO unwrapper, .net 3.5
  • ServiceStack.OrmLite I don't know much about this one.

Each project also lists a few other competitors; I'm sure you can find an appropriate choice amongst them that allows you to write plain sql with easily parametrized queries and have results be automatically unwrapped into plain objects. The performance overhead is virtually nil; so this solution is (IMHO) always preferable to raw data readers; it's so much easier in daily use.

Upvotes: 0

Jan Van Herck
Jan Van Herck

Reputation: 2284

Make a class Phone and change the User property accordingly:

public class Phone
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Number { get; set; }
}

public class User
{
    // (etc)
    public List<Phone> Phones { get; set; }
    // (etc)        
}

Make a PhoneRepository:

public class PhoneRepository
{
    public List<Phone> LoadPhonesFromId(IEnumerable<int> ids)
    {
       // write a query with the ids
       // SELECT * FROM Phones WHERE id IN (@id1, @id2, ...)
       // execute the query
       // convert the results to Phones and put them in a List<Phone>
       // return the list
    }
    public List<Phone> LoadPhonesFromUserId(IEnumerable<int> userIds)
    {
       // write a query with the userIds
       // SELECT * FROM Phones WHERE userId IN (@userId1, @userId2, ...)
       // execute the query
       // convert the results to Phones and put them in a List<Phone>
       // return the list
    }
}

And a UserRepository:

public class UserRepository
{
    public List<User> LoadUsersFromUserIds(IEnumerable<int> userIds)
    {
       // write a query with the userIds
       // SELECT * FROM User WHERE id IN (@id1, @id2, ...)
       // execute the query
       // convert the results to Users and put them in a List<User>
       // return the list
    }

    public void IncludePhones(IEnumerable<User> users)
    {            
        var phones = PhoneRepository.LoadPhonesFromUserId
            (users.Select(x => x.Id));

        for each(var user in users)
        {
            user.Phones = phones
                .Where(x => x.UserId == user.Id)
                .ToList();
        }
    }
}

You can greatly expand and improve on this pattern with, for example, custom filter parameters (instead of different functions for the various filters) and property setters that make sure the user and user.Phones keep referring to the same userID, but that is sort of beyond the scope of your question.

Upvotes: 2

Related Questions