Can i use nested queries for database entities in LINQ?

Question

I'm working on an ASP.Net application which uses a SQL-Server DB and database entities. Further i got three database entities which are dependend on each other. This is the dependency hierarchy:

Instance (Key: InstanceID)
- CustomField (Key: CustomFieldID, InstanceID)
- CustomFieldData (Keys: CustomFieldDataID, CustomFieldID)
  - CustomFieldData_Person (Keys: CustomFieldData_PersonID, CustomFieldDataID)

I can find out the entries from the entity CustomField by this with the InstanceID:

var customFieldEntries = DB_Instance_Singleton.getInstance.CustomField.Where(x => x.InstanceID == instanceId);

Now i want to find out all entries from CustomFieldData_Person which belong to the hierarchy with the InstanceID as key.

In SQL i would write something like this:

SELECT * FROM CustomFieldData_Person WHERE CustomFieldDataID in (
  SELECT * FROM CustomFieldData WHERE CustomFieldID in (
    SELECT * FROM CustomField WHERE InstanceID = instanceId))

Unfortunately i'm absolutely new to LINQ. So my question is, how can i write such a nested query in LINQ (aacording to the first code example above)?

Thanks in advance!

duplicate of stackoverflow.com/questions/9334919/linq-nested-query — Pranav Patel
– Pranav Patel, Commented May 27, 2016 at 8:57

MikeT · Accepted Answer · 2016-05-27 09:12:00Z

1

Firstly if you create your ER model correctly you will have most of that logic already set up for you

Person would have a property Person.CustomData which would have Properties for Field and Value so you can just navigate the object structure

however if you dont have that then you can just convert the in statements to Contains

CustomFieldData_Person.Where(cfdp=>CustomFieldData.Where(nested query for CustomFieldData).Contains(cfdp.CustomFieldDataID )

answered May 27, 2016 at 9:12

MikeT

5,5103 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zappasan · Accepted Answer · 2016-05-27 09:11:02Z

0

I think this link could be a good starting point for your question. Anyway take a look at Pranav's comment, it points to a helpful question

answered May 27, 2016 at 9:11

zappasan

8210 bronze badges

Collectives™ on Stack Overflow

Can i use nested queries for database entities in LINQ?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related