0

I'm stuck with this linq query.

I've this tables.

ID  A   B   C  D
1   some data 
2   some other data

Then, For every record on that table I may have none or many rows

ID  TableA_ID R
1   1         1
2   1         2
3   1         5
4   2         2

For example. Row 1 (some data) has 3 rows on table B.

I tried using 

tableA.Include(x => x.tablebchilds.Where( d => d.R == 1)).ToList()

but it is not working. With many others varation.

The objective of this query is to return tableA.row #1 if I pass it 1 as value (value of R). Number <> 2 won't give any result.

Tables are linked on EF. So TableB.tableA_ID is Foreign key of tableA.ID

Edit #1

I tried the answers in the question marked as duplicated with no luck. Give that 2 tableA.rows if the user insert 1 as parameter, linq query should return Row #1, some data. If 2 is passed as parameter, nothing is return.

A working SQL statement is:

SELECT [TableA].* FROM [TableA] JOIN [TableB] ON [TableA].[Id] = [TableB].[TableA_Id] WHERE [TableB].[R] = 1

Thanks!

Esselans
  • 1,540
  • 2
  • 24
  • 44
  • @Gert Arnold: I don't think it's a duplicate, because I think Engerlost is asking something else, though admittedly, it's not very clear. – jjj Jun 19 '15 at 17:32
  • 1
    @jjj Maybe. The point is that `Include` with `Where` isn't possible (sadly enough). This is being asked over and over again. There is a work-around which I describe in the duplicate. If the OP tries to achieve something else, yes it should be clarified. – Gert Arnold Jun 19 '15 at 17:58
  • @GertArnold The query on that answer won't work in my case. I know that Include with where is not possible. I'll edit my question to clarify that. Thanks! – Esselans Jun 19 '15 at 18:20
  • Sorry, I still can't quite follow what you're after. It looks like an `Any` query. Probably it helps to show working code and/or sample data (input and output). Also show where you've got navigation properties (like A.B). These invariably make life a lot easier. I'll reopen anyway, because it doesn't seem to be a duplicate, but it's liable to go down as "unclear what you're asking". – Gert Arnold Jun 19 '15 at 18:34
  • @GertArnold thanks Gert. I leave some data on Marc's answer. To sum it up: The SQL statement works OK. I'm trying to do that in linq ef. Much appreciated – Esselans Jun 19 '15 at 19:07
  • @GertArnold It's not so much that is doesn't work, its that BY DESIGN YOU SHOULD NOT BE ABLE TO DO IT. Because it's just damned confusing, the property no longer maps to what it says it maps to. – Aron Jun 22 '15 at 16:42
  • @Aron I'm not sure what doesn't work because I don't know what the OP wants. – Gert Arnold Jun 22 '15 at 19:15

1 Answers1

6

If you have database relationship properly configured this have to work.

tableA.Include(x => x.tableBChilds).Where(tableA => tableA.tableBChilds.Any(b => b.R== 1)).ToList();
Marc Cals
  • 2,963
  • 4
  • 30
  • 48
  • It doesn't work or I'm missing something. I edited my question with a working SQL statement. – Esselans Jun 19 '15 at 18:27
  • What happens? Exception message? – Marc Cals Jun 19 '15 at 18:32
  • When you say ´tableA.ID == 1´ **1** should be the **R** value not tableA.ID. I need whatever ´tableA´ rows have 1 in ´ tableB´. Thanks! – Esselans Jun 19 '15 at 19:05
  • Don't you say that R value is the foreing key of TableA.ID in your answer? If this it's true filtering `tableA.ID == 1 ` you are selecting tableA row with TableBChild.R == 1 – Marc Cals Jun 19 '15 at 19:09
  • Sorry. I made a typo there. TableB has a FK to TableA and the R value. If **1** is the param it should return tableA.row#1. In case of **2**, both rows are returned. (I edited the original question with the new data) Thanks – Esselans Jun 21 '15 at 02:12
  • When I write tableA.tableBChilds.R (R is not in the intellisense) and it throws an error. I can't access any tableB properties this way. – Esselans Jun 22 '15 at 14:29
  • Sorry I forgot that TableChilds was a collection, answer updated – Marc Cals Jun 22 '15 at 16:19