Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
TABLE A:

ColumnA  ,  ColumnB  ,   ColumnC   ,   ColumnD


TABLE B:

ColumnA  ,  ColumnB  ,   ColumnC   ,   ColumnD




how do I select two query on the same columns and find partners with intersect?



I want to table a => select a,b,c column where d=1

I want to table b => select a,b,c



I want to do intersect and find common a, b, c columns. And I want to return these columns with foreach.

What I have tried:

var tableA: MyStudentEntities.Select(x=> new
{
ColumnA=x.ColumnA
ColumnB=x.ColumnB
ColumnC=x.ColumnC

}).Where(x=>x.ColumnD=1); //this line is not run

var tableB:
MyStudentEntities.Select(x=> new 
{
   ColumnA=x.ColumnA
   ColumnB=x.ColumnB
   ColumnC=x.ColumnC

}).ToList();

foreach(var item in intersect)
{


}
Posted
Updated 29-Dec-19 17:42pm

The Where condition can only use properties from the projection it operates on. Move the Where before the Select (and fix the other syntax errors) and it will work.
C#
var tableA = MyStudentEntities.Where(x => x.ColumnD == 1).Select(x => new
{
    x.ColumnA,
    x.ColumnB,
    x.ColumnC
});
 
Share this answer
 
Comments
Maciej Los 17-Dec-19 14:49pm    
5ed!
Conjecturaly speaking, you will want to make your selections first and then intersect them. So, in SQL, you would do it this way:
SQL
SELECT 
    A.ColumnA,
    A.ColumnB,
    A.ColumnC,
    A.ColumnD
FROM tableA AS A
INNER JOIN tableB AS B
    ON A.ColumnA = B.ColumnA
    AND A.ColumnB = B.ColumnB
    AND A.ColumnC = B.ColumnC
WHERE A.ColumnD = 1

The problem is that this is difficult to represent using Lambdas with DataEntity classes, mostly because it is hard to shape the dynamic SQL the ORM creates on the fly. So you go for the next best thing.

You select your first entity list from tableA:
C#
var studentsA = MyStudentEntitiesA
    .Where(a => a.ColumnD == 1);

You select your second entity from tableB:
C#
var studentsB = MyStudentEntitiesB
    .Where(b => studentsA.Any(a =>
        a.ColumnA == b.ColumnA &&
        a.ColumnB == b.ColumnB &&
        a.ColumnC == b.ColumnC)
    );


And now you can do your foreach statement:

C#
foreach (var b in studentsB)
{
    Console.WriteLine($"ColumnA:{b.ColumnA}, ColumnB:{b.ColumnB}, ColumnC:{b.ColumnC}");
}
 
Share this answer
 
Try this for select from table
var selectedTableAList= (Your DB Context).tableA.Where(x => x.ColumnD == 1).Select(x => new
{
    x.ColumnA,
    x.ColumnB,
    x.ColumnC
})
 
Share this answer
 
Comments
Richard MacCutchan 30-Dec-19 4:26am    
This is the second tome you have copied an existing Solution and presented it as your own. Be careful or you may end up being cited for abuse.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900