mysql - C# filtered read of several tables with multiple relations -

i have quite large database several tables , complex relations , thousands of records. simplify problem, have got mysql database follows:

[table1]

• id
• table2_id
• table3_id
• name_id

[table2]

• id
• name_id

[table3]

• id
• name_id

[name]

• id
• text

then there relations:

• table1.name_id -> name.id
• table1.table2_id -> table2.id
• table1.table3_id -> table3.id
• table2.name_id -> name.id
• table3.name_id -> name.id

tables contain:

• 50,000 records in table1
• 10,000 records in table2
• 5,000 records in table3
• 25,000 records in name

i need display table1 in datagridview showing in columns text names.

i need filter applied every table1 request can include name search keyword each table: table1, table2, table3.

i have been trying manually fill dataset using dataadapters , adding datarelations , found problems along:

• it takes ages , lot of memory dataset filled data. not need @ once, happy if limit table1 5,000 records filter applied
• considering dataadapters , datasets, not manage set conditions child rows in order retrieve filtered table1 data - example: want list of table1 table2 name text = "a" , table3 text = "b"

i not want build 'select ... left join' or 'select .... table1, table2, table3' queries mentioned before, deal more 6 tables nested relations (eg. related table in relation 1 etc.)

i not want retrieve table1 records , filter them @ c# side said before - takes long time , there no need display records on screen. records must considered in terms of filtering records result can limited 5,000 items.

i understand using dataset , dataadapters may wrong in scenario. other ideas welcome , sure helpful well. thank you.

could please point me in right direction?