Query SQL with 2 subselects for query lambda (or Linq)

Asked

Viewed 1,637 times

4

select fieldA from TableA
where fieldA = 459249 and
fieldB in (
    select FieldC
    from TableB
    where FieldD in
    (
        select cte.FieldE
        from TableC cte
        where key= 'ABDC123'
    )
)

I managed to get to the point below, the project does not compile:

TableA.Where(x => x.FieldA == 459249)
   .Select(x => x.FieldA .Contains(TableB.Select(i => i.FieldB).Contains
   (
    TableC.Where(c => c.FieldC== "ABDC123").Select(c => c.FieldD).ToString()
   )
));
  • Do not compile? What is the build error?

  • The error happens here Tableb.Select(i => i.Fieldb).Contains. The type cannot be inferred from the Usage. Try specifying the type Arguments explicity. I tried typing but get error message "Cannot resolve method Contains (string).

2 answers

1


This is because Select brings a field that is not exactly a IEnumerable. Contains exists only for IEnumerables.

First, replace the first two Select for SelectMany (bringing together all the elements selected in one IEnumerable).

Try not to use Contains outside the Where. In this case, Intersect works best.

TableA.Where(x => x.FieldA == 459249)
      .SelectMany(x => x.FieldA).Intersect(TableB
                  .SelectMany(i => i.FieldB)
                  .Intersect(TableC
                            .Where(c => c.FieldC == "ABDC123")
                            .Select(c => c.FieldD))
                  )
              );
  • .Selectmany(x => x.FieldA.Contains... x.FieldA.Contains in this part does not show the option . Contains has only a few options like Tostring, Compareto etc

  • Ah, yes. Pardon. I’ll correct.

  • @Thiagocrizanto See now.

  • Guy thought this would work. It gave problem again in the second select (Selectmany). Error: The type Arguments cannot be inferred from the Usage. Try specifying the type Arguments explicitly. I tried to type this guy but here the errors come as in the first select I made.

  • Makes sense. Contains return bool. I switched to Intersect.

  • You’re the man! The project ran... but I’m having a runtime problem when I run this guy. Dbexpressionbinding requires an input Expression with a Collection Resulttype. It has been helpful and I will continue in the fight! Valew!

Show 1 more comment

0

I believe this way you can achieve:

var resultado = from tableA in TableA
join tableB in TableB on tableA.FieldB equals tableB.FieldC
join tableC in TableC on tableB.FieldD equals TableC.FieldE
where tableA.FieldA == 459249 && tableC.key = "ABDC123"
select tableA.FieldA;

Note: I haven’t been able to test this code yet.

  • Cara did not give because already in the first select I need to check if there are already results in the list.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.