I have a string array, I would like to select all rows from Table 1 that match any values of this array with values of stringA. I would then like to take the table1IDs from these rows and select any rows in Table 3 that match any of these table1IDs. Finally
I would like to take the table2IDs from these rows and select all rows from table 1 that have a matching table2ID.
I am wanting to know what the most code efficient way of achieving this is using a Linq to SQL select statement is(with vb.net)
My first post was my attempt to simplify things as much as I could to make it understandable, obviously I did not do a good job.
I have two problems, the first is that I have an array of names e.g. firstnames as string() = {bob, fred, john}, when I write a linq to sql select statement with a where clause like "where names.firstname = firstnames" I get an exception because firstname
is a string while firstnames is a string array. I could get round this by having a where clause that read "where names.firstname = bob Or names.firstname = fred"... but the names in the array and the length of the array change so this would not work for all
cases.
The second problem is selecting data from one table where the where clause is based on the selection from a different table e.g. getting a load of employee IDs from an employees table, then using these IDs to get their first names from a second details table
I'm guessing it would look something like;
dim EmployeeFirstnames = from n in myContext.tblDetails
where n.personID = (from e in myContext.tblEmployees
where e.employeeType = (array of employee types as string)
In fact you can use IndexOf method, please try this:
dim EmployeeFirstnames = from n in myContext.tblDetails
where n.personID = (from e in myContext.tblEmployees.AsEnumerable()
where Array.IndexOf(array of employee types as string,e.employeeType)>=0
select e.employeeID)
select n.firstname
hogg1980
Member
38 Points
33 Posts
How to select data from one table based on the results of a select from a second (or third...)
Nov 27, 2012 09:22 PM|LINK
Imagine I have three tables;
Table 1
Column 1: StringA (String)
Column 2: Table1ID (integer primary key)
Table 2
Column 1: StringB (String)
Column 2: Table2ID (integer primary key)
Table 3
Column 1: Table1ID (integer composite primary key, foreign key constraint with table1 Table1ID)
Column 2: Table2ID (integer composite primary key, foreign key constraint with table2 Table2ID)
I have a string array, I would like to select all rows from Table 1 that match any values of this array with values of stringA. I would then like to take the table1IDs from these rows and select any rows in Table 3 that match any of these table1IDs. Finally I would like to take the table2IDs from these rows and select all rows from table 1 that have a matching table2ID.
I am wanting to know what the most code efficient way of achieving this is using a Linq to SQL select statement is(with vb.net)
Thanks for any support
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: How to select data from one table based on the results of a select from a second (or third......
Nov 28, 2012 04:50 AM|LINK
Hello,
Your logic seems a little complicated……Well……Can you give us an example of real data in your tables and step-by-step tell us your acutal wish?
Reguards!
hogg1980
Member
38 Points
33 Posts
Re: How to select data from one table based on the results of a select from a second (or third......
Nov 28, 2012 10:32 AM|LINK
My first post was my attempt to simplify things as much as I could to make it understandable, obviously I did not do a good job.
I have two problems, the first is that I have an array of names e.g. firstnames as string() = {bob, fred, john}, when I write a linq to sql select statement with a where clause like "where names.firstname = firstnames" I get an exception because firstname is a string while firstnames is a string array. I could get round this by having a where clause that read "where names.firstname = bob Or names.firstname = fred"... but the names in the array and the length of the array change so this would not work for all cases.
The second problem is selecting data from one table where the where clause is based on the selection from a different table e.g. getting a load of employee IDs from an employees table, then using these IDs to get their first names from a second details table
I'm guessing it would look something like;
dim EmployeeFirstnames = from n in myContext.tblDetails
where n.personID = (from e in myContext.tblEmployees
where e.employeeType = (array of employee types as string)
select e.employeeID)
select n.firstname
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: How to select data from one table based on the results of a select from a second (or third......
Nov 29, 2012 12:12 AM|LINK
Hello again,
In fact you can use IndexOf method, please try this:
dim EmployeeFirstnames = from n in myContext.tblDetails where n.personID = (from e in myContext.tblEmployees.AsEnumerable() where Array.IndexOf(array of employee types as string,e.employeeType)>=0 select e.employeeID) select n.firstname