I am using Entity Framework. I want to select data from multiple tables in the database having no foreign key relation like
select
tOut.columnId, wo.columnType, tIn.*
from
TbaleA tIn,
TableB tOut,
TableC wo
where
1 = 1
and tIn.columnRefId = tOut.columnGuid
and tOut.columnId = wo.columnId
Is there any solution in Entity Framework for this? I have tried by using include syntax its not working for me ..
You can join tables even though no foreign key relation exists if you use the query syntax. It would go something along these lines:
var result = from tIn in yourDbContext.TableA
join tOut in yourDbContext.TableB on tIn.columnRefId equals tOut.columnGuid
join wo in yourDbContext.TableC on tOut.columnId equals wo.columnId
select new { tOut.columnId, wo.columnType, TableA = tIn };
There are examples for both lambda and query syntax on these links:
Another solution is to use/add navigation properties to your classes, as for example:
class TableA {
Int32 Id {get; set;}
//navigation property
TableB b {get; set;}
}
and use them in the query
from a in yourDbContext.TableA
select new { a.Id, a.b.columnType}
Of course there may be a little configuration (code or annotation) to setup the relations to make them fit the database schema. But you will do this effort once and not for every query.
