CODEWITHSUNDEEP
linqentity-frameworkmany-to-many
nasa
nasa

Reputation: 11

how to select data by linq in many-to-many relationship in First code Entity framework 5

I make a database by using first code entity framework 5. In my model, I have some many-to-many relationship between my entities. I don't have any idea how i can fetching data in these model.

I have these classes.

public abstract class BaseEntity
{
    public int Id { get; set; }
}

public class UserGroupEntity : BaseEntity
{
    public UserGroupEntity()
    {
        this.Users = new List<UserEntity>();
        this.Exams = new List<ExamEntity>();
    }

    public string Name { get; set; }
    public byte State { get; set; }

    public virtual IList<UserEntity> Users { get; set; }
    public virtual IList<ExamEntity> Exams { get; set; }
}

public class UserEntity : BaseEntity
{
    public UserEntity()
    {
        UserGroups = new List<UserGroupEntity>();
    }

    public string UserName { get; set; }
    public byte State { get; set; }

    public virtual IList<UserGroupEntity> UserGroups { get; set; }
}

public class ExamGroupEntity : BaseEntity
{
    public ExamGroupEntity()
    {
        this.Exams = new List<ExamEntity>();
    }

    public string Name { get; set; }
    public byte State { get; set; }

    public virtual IList<ExamEntity> Exams { get; set; }
}

public class ExamEntity : BaseEntity
{
    public ExamEntity()
    {
        this.UserGroups = new List<UserGroupEntity>();
        this.ExamDetail = new List<ExamDetailEntity>();
    }

    public string Name { get; set; }
    public byte State { get; set; }

    public virtual ExamGroupEntity ExamGroup { get; set; }
    public virtual IList<UserGroupEntity> UserGroups { get; set; }
}

And their relationship is :

    public class UserMapping : BaseMapping<UserEntity>
{
    public UserMapping()
        : base("Users")
    {
       // Many2Many
        this.HasMany(x => x.UserGroups)
            .WithMany(x => x.Users)
            .Map(map =>
            {
                map.MapLeftKey("UserId");
                map.MapRightKey("UserGroupId");
                map.ToTable("UsersJoinUserGroups");
            });
    }
}
public class ExamMapping : BaseMapping<ExamEntity>
{
    public ExamMapping()
        : base()
    {
        // Many2Many
        this.HasMany(x => x.UserGroups)
            .WithMany(x => x.Exams)
            .Map(map =>
            {
                map.MapLeftKey("ExamId");
                map.MapRightKey("UserGroupId");
                map.ToTable("ExamsJoinUserGroups");
            });
    }
}

How can I write a Linq expression which is equal this Sql statement?

 select e.*
from Exams e join ExamsJoinUserGroups EJU
    on e.Id= eju.ExamId join UsersJoinUserGroups UJU
    on EJU.UserGroupId = UJU.UserGroupId
where UJU.UserId=2
    and e.ExamGroup_Id=1
    and e.State=1

Upvotes: 1

Views: 285

Answers (2)

Slauma
Slauma

Reputation: 177173

With your model the LINQ query could look like this:

var exams = context.ExamEntities
    .Where(e => e.UserGroups.Any(ug => ug.Users.Any(u => u.Id == 2)) &&
                e.ExamGroup.Id == 1 &&
                e.State == 1)
    .ToList();

Upvotes: 1

Adam Diament
Adam Diament

Reputation: 4880

Joining in Linq requires you to use the equals key word instead of the = symbol in the join part of the query, but then to switch back to C# operands e.g. == in the where clause.

Following the syntax

"From x in ListOfX join y in ListOfY on x.Id equals y.Id select....."

var joined = 
(
from e in Exams join EJU in ExamsJoinUserGroups
on e.Id equals EJU.ExamId
join UJU in UsersJoinUserGroups
on EJU.UserGroupId equals UJU.UserGroupId 

where UJU.UserId == 2
&& e.ExamGroup_Id == 1
&& e.State == 1

select e
);

Upvotes: 0

Related Questions