0

Suppose that I have this two tables:

  • TableA(IDTableA, ...)
  • TableB(IDTableB, IDTableA, ...)

I would like a subquery with LinQ like this:

select * 
from MytableA 
where IDTableA = (select IDTable A From TableB where IDTableB = 123);

I am trying something like this:

MytableA myTableAEntity = dbContext.MytableA
.Where(x=>x.IDTableA == dbContext.MytableB.Where(y=>y.IDTableB == 123).SingleOrDefault();

But I have an error that says that I can't cast IQueryable<long> into a long.

Is it possible to do something like that?

Thank so much.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Álvaro García
  • 18,114
  • 30
  • 102
  • 193

1 Answers1

2

In your first Where clause you are testing equality of incompatible types of long and MytableB.

You have to select IDTableA in your subquery before comparing:

IEnumerable<MytableA> matchedTableAs = dbContext.MytableA
    .Where(x=>x.IDTableA == dbContext.MytableB.Where(y=>y.IDTableB == 123)
        .Select(b => b.IDTableA).SingleOrDefault());
Saravana
  • 37,852
  • 18
  • 100
  • 108
  • But I think with this solution that the SingleOrDefault do a quiery to database and then the main where another query, so I need two trip to the database. I would like to avoid this and make only one trip. – Álvaro García Jan 03 '15 at 18:41
  • No, EF generates one SQL statement for this. – Gert Arnold Jan 03 '15 at 19:04
  • And there is any way to check if EF does two trips or only one? This question is to learn how to depure my code. – Álvaro García Jan 03 '15 at 19:20
  • 1
    @ÁlvaroGarcía You can use LINQpad to view the generated SQL. Or use the `ToTraceString()` method described here: http://stackoverflow.com/a/1412902/2419531. – Saravana Jan 04 '15 at 03:05