CODEWITHSUNDEEP
c#entity-framework
sa227
sa227

Reputation: 37

How to get multiple Records from Second table base on list of record from First table Using EF

I have two tables in Sql database, say Users and UserEducation table. There are multiple records in user table and for each user there is one record in UserEducation Table. I want to show all records in grid-view by joining these two table? How can i do this using Entity Framework?

Upvotes: 1

Views: 575

Answers (4)

RBT
RBT

Reputation: 25887

I created a console application to meet your use case. You will have to reuse this code into your windows forms or web based application where you want to bind the output to a grid view. I've assumed some properties in the POCO classes, you can always modify them based on what all values you are saving for your user and education entities. I've not mentioned any connection string in my code snippet. Entity framework automatically connects to a sql express database on your computer if you don't mention a connection string or it will connect to the database for which you have mentioned the connection string in the app.config or web.config file. Hope this helps!

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace UsersCodeFirst
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new EfContext())
            {
                // Display all users with their education from the database 
                var query = from user in db.Users 
                           join userEducation in db.UserEducations
                           on user.UserId equals userEducation.UserId
                    orderby user.Name
                    select new
                    {
                        Name = user.Name,
                        UserEducation = userEducation.CourseName
                    };

                //bind to grid by setting grid data source to query.ToList()
            }

            Console.WriteLine("Press any key to exit...");
            Console.ReadKey();
        }
    }

    public class User
    {
        public string UserId { get; set; }
        public string Name { get; set; }
        public virtual List<Education> Posts { get; set; }
    }

    public class Education
    {
        public string EducationId { get; set; }
        public string CourseName { get; set; }
        public string UserId { get; set; }
    }

    public class EfContext : DbContext
    {
        public DbSet<Education> UserEducations { get; set; }
        public DbSet<User> Users { get; set; }
    }
}

You will need to add Entity Framework nuget package into your project to get reference to DbContext class.

Upvotes: 1

Vladimir
Vladimir

Reputation: 1390

Or using Linq to Entity and join tables.: 

using ( var context = new YourContext)
   {
     var users = context.UserDbSet;
     var userEdications = context.UserEdication.DbSet ;

     var joinedTables =  from user in users
                         join userEdication in userEdications on user.userId  = userEdication.userId 
                         select new OutPutEntity
                         {
                               Name = user.Name,
                               Edication = userEdication.Edication
                         }

                         gridView.DataSource = joinedTables.toList(); // should be placed outside the using. (here just as a sample)
   }

Advantage - you can specify output format on IQurable level. Downside - it`s looks complicated.

Upvotes: 0

Gaurav Taneja
Gaurav Taneja

Reputation: 11

since there is one to one mapping, I would prefer making one table. But specifically you can have like:

entityframeworkContext obj = new entityframeworkContext(); List xyz = obj.database.SqlQuery("select u.fieldname1 as modeltablefield1, u.fieldname2 as modeltablefield2, ued.fieldname1 as modeltablefield3, ued.fieldname2 as modeltablefield4 from Users u inner join UserEducation ued on u.commonfield = ued.commonfield");

Here common field will be foreign key in the second table Model Table is any logical table required by you from the combined query (MVC specific) Hope it works for you !!

Upvotes: 1

Marcel B
Marcel B

Reputation: 518

You can simply use the navigationproperty from User:

var user = new User();
user.UserEducation.[Property];

Upvotes: 1

Related Questions