CODEWITHSUNDEEP
linqentity-framework-corerepository-pattern
Nemesis
Nemesis

Reputation: 123

Join between two repositories in Entity Framework Core

Table A 

a_id (PK) 
aid_code 
aid_desc

a_id |  aid_code |  aid_desc
----    --------    --------
1301 |  FN       |  FAN
1302 |  PN       |  PAN
1303 |  LN       |  LAN

-> Table A has one column primary key.

Table B

b_id (PK)
b_enddate (PK)
a_id (PK)
b_dob
b_name
code (not mapped property)

-> Table B has composite primary key using 3 columns.

b_id |  b_endate    |   a_id |  b_dob       |   b_name
----    ---------       ----    ----------      ------
1    |  01/01/2020  |   1301 |  01/01/2017  |   sam
1    |  10/02/2020  |   1302 |  02/01/2016  |   ham
2    |  01/10/2022  |   1303 |  03/01/2016  |   jam
3    |  11/10/2023  |   1302 |  05/01/2015  |   bam

There is a generic repository that handles only one entity class for each Table A and Table B.

var a = context.GetRepository<A>();
var b = context.GetRepository<B>();

The consumer will call the API this way: {apiroute}/?id=1&code=FN

I've to filter by id which is b_id column value in TableB and code which is aid_code column value in Table A but TableB only has a_id column value from Table A.

I added additional not mapped property says Code in Table B entity to hold the value for aid_code from Table A and did a join between Entity API and Entity B. I'm creating dynamic object using select new EntityB{ code = entityA.aid_code, ..... }

var records = from a in entityA
              join b in entityB on b.a_id equals a.a_id
             select new  EntityB{ b_id = b.b_id, b_enddate = b.b_enddate, code = entityA.aid_code, ..... }

The return type has to be IQueryable<EntityB> as I'm doing ToList() later on.

I've to do the filtering towards the end by preparing a concatenated where clause but the filtering by where clause is not working. And it doing a join between Tables A and B ignoring the where clause which is running forever.

What am I doing wrong?

Upvotes: 1

Views: 1556

Answers (1)

Nemesis
Nemesis

Reputation: 123

All, I resolved this issue using include instead of doing a join and returning dynamic object. Firstly, I added the one to many relationship between the entities in the DBContext (as I'm using EntityFrameworkCore) as given below:

//This is the modelBuilder for EntityA 

entity.HasMany(m => m.EntityB)
                .WithOne(c => c.EnitityA)
                .HasForeignKey(k => k.a_id );

Add the corresponding navigation property in EntityB as given below:

public virtual EntityA EntityA { get; set; }

Likewise, add the corresponding property for navigation in EntityA as given below:

public virtual ICollection<EntityB> EntityB { get; set; }

Then finally include:

var query = repoB.GetAll().Include("EntityA").AsNoTracking() as IQueryable<EntityB>

Then do the where clause on above query as:

var returnVal = query.Where(x => x.EntityB.aid_code == paramValue):
var result = returnVal.ToList();

Upvotes: 1

Related Questions