C# Entity Framework: Mapping select results to list of objects

Question

Let's say I have a list of Student objects and I want to find their tuition. Their tuition is in a database and the key is their student id. I want to map that tuition into the student objects after. So I have

Class Student{
    int Id;
    string Name;
    double GPA;
    double Tuition; 
}

    List<Double> GetTuitionsById(List<Student> students)
    {
        var ids = students.Select(x => x.Id).ToList();
        var query = from dbStudent in _context.StudentsDB
                    where ids.Contains(dbStudent.Id)
                    select dbStudent.Tuition;

        var tuitions = query.ToList();

        return tuitions;
    }

I was then hoping to loop through tuitions and map it into the students list, but the select query doesn't return the prices in the same order. So how can I map the tuitions from the DB to the student objects?

krivtom · Accepted Answer · 2015-03-21 07:27:01Z

1

When you have no ordering in the database query the results coming from the database are not guaranteed to be in any specific order. To return the results in the order they were passed in you need to return Id from the database so the data can be mapped to initial list and then create the list manually:

List<Double> GetTuitionsById(List<Student> students)
{
    var ids = students.Select(x => x.Id).ToList();
    // return Id and tuition
    var query = from dbStudent in _context.StudentsDB
                where ids.Contains(dbStudent.Id)
                select new { dbStudent.Id, dbStudent.Tuition};  

    var tuitions = query.ToList();

    var results = List<double>();
    foreach(var student in students)
    {
        // below is assumed that every student has tuition returned from the database
        var foundTuition = tuitions.First(t => t.Id == student.Id);
        results.Add(foundTuition.Tuition);
    }
    return results;
}

Edit: @pwas correctly pointed out that using Dictionary would give a better performance:

List<Double> GetTuitionsById(List<Student> students)
{
    var ids = students.Select(x => x.Id).ToList();
    // return Id and tuition
    var query = from dbStudent in _context.StudentsDB
                where ids.Contains(dbStudent.Id)
                select new { dbStudent.Id, dbStudent.Tuition};  

    var tuitions = query.AsEnumerable().ToDictionary(s => s.Id, s => x.Tuition);

    var results = List<double>();
    foreach(var student in students)
    {
        // below is assumed that every student has tuition returned from the database
        results.Add(tuitions[student.Id]);
    }
    return results;
}

edited Mar 21, 2015 at 7:27

answered Mar 21, 2015 at 6:52

krivtom

25k10 gold badges53 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2160375 Over a year ago

Small improvement / hint: You code works in O(n^2). So you can make tuitions a dictionary: query.AsEnumerable().ToDictionary(s => s.Id, s => x.Tuition). Then, in foreach: var foundTuitions => tuitions[student.Id]. Thanks to this, code will work in 2 * O(n), so it could be faster when many students are in the list ;)

krivtom Over a year ago

@pwas That's an excellent point! I will update the answer to include this option

roverred Over a year ago

Then would not making var ids = students.Select(x => x.Id).ToList();into a dictionary instead also be better then? So we can replace where ids.Contains(dbStudent.Id) to ids.ContainsKey(dbStudent.Id).

krivtom Over a year ago

@roverred The part of query that you are referring to is executed by Entity Framework, and it does not support dictionary. If you make the changes that you are referring to you would get a runtime exception

A_Sk · Accepted Answer · 2015-03-21 06:50:26Z

0

try .

 List<Double> GetTuitionsById(List<Student> students)
    {
     List<Double> objRes=new List<Double>();
        var ids = students.Select(x => x.Id).ToList();
        var query = (from dbStudent in _context.StudentsDB
                    where ids.Contains(dbStudent.Id)
                    select dbStudent.Tuition).ToLIst();
         foreach(var x in query )
         {
          objRes.Add(Double.Parse(x));
         }

        return objRes;
    }

answered Mar 21, 2015 at 6:50

A_Sk

4,6505 gold badges31 silver badges55 bronze badges

Collectives™ on Stack Overflow

C# Entity Framework: Mapping select results to list of objects

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related