CODEWITHSUNDEEP
c#linqframeworks
VeecoTech
VeecoTech

Reputation: 2143

3.5 VS 4.0 .NET Framework

5 framework, not using relationship foreign key in database, and i wonder how 4.0 can improve this junk of code which i need to pass back the multiple object after multiple joins of tables.

  public IList<User> GetTutorByCourseId(int courseId)
    {
        IList<User> output = new List<User>();
        using (leDataContext db = new leDataContext())
        {
            try
            {
                var m = from c in db.Courses
                        join ct in db.CourseByTutors on c.Id equals ct.CourseId
                        join u in db.Users on ct.TutorId equals u.Id
                        where c.Id == courseId
                        select new
                        {
                            c, ct, u
                        };

                foreach (var result in m)
                {
                    User user = new User();
                    user.Id = result.u.Id;
                    user.Name = result.u.Name;
                    user.CourseTutor.Id = result.ct.Id;
                    user.Course.Name = result.c.Name;    
                    output.Add(user);
                }
                return output;
            }
            catch (Exception ex)
            {
                Logger.Error(typeof(User), ex.ToString());
                throw;
            }
        }
    }

There are 3 objects being return to the caller in GUI. However, to do this i got to add the property of public CourseByTutors{get;set} and the public Course(get;set;) in the User class which i find that it will mess up my code. In this case, how would 4.0 able to solve this? i read something about select tupel .. ??

Upvotes: 3

Views: 1935

Answers (3)

Aasmund Eldhuset
Aasmund Eldhuset

Reputation: 37940

What about this (in 3.5)?

select new User
{
    Id = u.Id,
    Name = u.Name,
    CourseTutor = new CourseTutor {Id = ct.Id},
    Course = new Course {Name = c.Name}
};
return m.ToList();

EDIT: Replaced illegal CourseTutor.Name and Course.Id initializers. The corrected code will work, assuming that the constructor of User didn't do any fancy initialization of CourseTutor and Course.

Upvotes: 3

dubs
dubs

Reputation: 6521

Even though you don't have foreign keys in the database, there's no reason that you can't add relationships into your EF model. This will help simplify the problem as you will not have to generate additional properties to store your child values; CourseTutor, CourseName etc..

Both 3.5 and 4.0 can help out here, although, in my experience this is much easier in 4.0.

Once the your code above might look something like:

var results = (from u in db.Users
        where u.Course.Id == courseId
        select u).ToList();
return results;

Hope this helps.

Upvotes: 2

tvanfosson
tvanfosson

Reputation: 532445

The way that I would normally handle this is to have a separate model from my data entities for the GUI that includes just the information that the GUI needs. You could do this in 3.5 if you want.

public class TutorViewModel
{
     public IEnumerable<User> Tutors { get; set; }
     // the pair CourseId, UserId is the relation in CourseTutors so we only
     // need to keep it once, not once per user.
     public int CourseId { get; set; } 
     public string CourseName { get; set; }
}



public TutorViewModel GetTutorByCourseId(int courseId)
{
    var model = new TutorViewModel { CourseId = courseId };

    using (leDataContext db = new leDataContext())
    {
        try
        {
            model.CourseName = db.Courses
                                 .First( c => c.CourseId == courseId )
                                 .Name;
            model.Users = db.CourseByTutors
                            .Where( c => c.Id == courseId )
                            .Join( db.Users,
                                   c => c.TutorId,
                                   u => u.Id,
                                   (c,u) => u );

            return model;
        }
        catch (Exception ex)
        {
            Logger.Error(typeof(User), ex.ToString());
            throw;
        }
    }
}

Upvotes: 0

Related Questions