How to show two tables records in one foreach?

Question

How can I show two tables's records in one foreach at sametime via Entity Framework?

My tables:

My code:

var transfer = dbContext.tbl_Transfer.Where(a => a.GeciciServisID == x.GeciciServisID);

Both tables have GeciciServisID, so I would like to get them with GeciciServisID. Then I would like to process records like

foreach (var x in transfer){}

Is there a simple way to do it?

Thanks you.

UPDATE:

jim tollan put the solution but, I explained wrong my question. I mean, as migrate or as merge O_o two table with code. for example we have two table (as you see)

Table A / Table B

1. ABC / 1.ABS

2. EBS / 2. EDC

3. RAW / 3. HTML

and then foreach:

• ABC

• ABS

• EBS

• EDC

• RAW

• HTML

I think my question is clear to understand now... :)

FINAL:

Jim Tollan finished.

Thanks.

Answer 1

ercin, based on Esa's answer, here's the c# version:

var results = 
    tbl_EkTransfer
    .Join(tbl_Transfer, tt => tt.GeciciServisId, tkt => tkt.GeciciServisId, 
        (newtt, newtkt) => new
        {
            newtt, 
            newtkt
        })
        .Where(x => x.newtkt.GeciciServisId == 1); // etc

you'd then iterate doing:

foreach (var result in results)
{
    var test1 = result.newtkt.GeciciServisId;
    var test2 = result.newtt.GeciciServisId;
}

[Edit] - as per the OP amendment to the question.

Amendment

This implementation is a very simple multi-step approach, rather than trying to create a linq super complex implementation. Literally, we take the two IQueryable datasets and add them to a new TransferClass(). It's not complex and is purely a starting point to experiment with. We can't union the structures as they are different, so we adopt a holding class to project into. Anyway, the update:

the new holding class (a sort of viewModel):

public class TransferResult
{
    public int GeciciServisId { get; set; }
    public int EkipID { get; set; }
    public string Semt { get; set; }
    public string Adres { get; set; }
    public string SourceTable { get; set; }
}

the implementation:

// create a holding class collection
var results = new List<TransferResult>();

// add the results from first table
results.AddRange(tbl_EkTransfer.Where(x => x.GeciciServisId == 5)
    .Select(x => new TransferResult()
{
    GeciciServisId = x.GeciciServisId,
    Adres = x.Adres,
    Semt = x.Semt,
    EkipID = x.EkipID,
    SourceTable = "tbl_EkTransfer"
}));

// add the results from second table
results.AddRange(tbl_Transfer.Where(x => x.GeciciServisId == 5)
    .Select(x => new TransferResult()
{
    GeciciServisId = x.GeciciServisId,
    Adres = x.Adres,
    Semt = x.Semt,
    EkipID = x.EkipID,
    SourceTable = "tbl_Transfer"
}));

// now one row per joined result set
foreach (var result in results.OrderBy(x => x.Adres))
{
    var test1 = result.Adres; // etc
}

That's really all there is to it. This does take away the linq join but sacrifices nothing to achieve the result. Given more time, I'm certain we could still use a join and do the operation in a single hit, but that's an essay for another day...

Answer 2

Based on Esa's answer, I have manually converted to C#:

var results = from ek in dbContext.tbl_EKTransfer
              where //...(what you want)
              select ek.ServisID
var results = from t in dbContext.tbl_Transfer
              where // ...(what you want)
              select t.ServisID;

results = results.Union(results2);

foreach (var result in results)
{

}

Update

I've updated the sample to show getting the values as you specify in your update. For this I have assumed you want a single, shared value from both - with a shared datatype.

If you want multiple columns, you need to replace the select line with select new() {ek.ServisID, ek.TransferId} where the values specified are the values you want. Be aware though, that both queries need to output exactly the same anonymous type - that is, they both must have the same properties, with the same name, and the same datatype.

You can get around different names by aliasing in the new: new {ID = ek.SomeOtherColumn}

Answer 3

You can join the tables like this. Sorry it's in VB.Net but someone can probably translate it to C# if needed:

Dim results = From ek in tbl_EkTransfer 
              Join t in tbl_Transfer on ek.GeciciServisId = t.GeciciServisId
              Where... (what you want)
              Select ek, t

For Each result in results
  ' ...
Next