ImInOrlando
asked on
Linq To Sql Dynamic Query using Collection Object Visual Studio 2008
I am attempting to try to use the following Linq query to pull for multiple values of 'ID'. Instead of creating a complicated LINQ query with numerous 'where' clauses, is it possible to query against only the ID values in a collection object?
My Collection is an ArrayList object
- it contains various ID values we will query for
My Linq Query:
// Query the Table
IQueryable query = from c in Table
where c.ID == System.Convert.ToInt32(arr Providers[ i])
select new { c.Number,
c.FName,
c.LName };
My Collection is an ArrayList object
- it contains various ID values we will query for
My Linq Query:
// Query the Table
IQueryable query = from c in Table
where c.ID == System.Convert.ToInt32(arr
select new { c.Number,
c.FName,
c.LName };
ASKER
I am still struggling with this question. Can someone please help? Here is my most updated code throwing an error. Basically I am trying to Select records from a SQL Table based on the ID value stored in an ArrayList object.
Code:
ArrayList arrSet = new ArrayList();
arrSet.Add(new Order{ID=1});
arrSet.Add(new Order{ID=2});
arrSet.Add(new Order{ID=5});
Table<Customer> customers = DataContext.GetTable<Custo mer>();
LINQ Query Code:
from c in customers.AsQueryable()
join p in arrSet.Cast<Order>()
on c.ID.Value.ToString() equals p.ID.ToString()
select new { c.FirstName, c.LastName };
Error Message I am receiving:
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.
Can someone tell me whats going on?
Code:
ArrayList arrSet = new ArrayList();
arrSet.Add(new Order{ID=1});
arrSet.Add(new Order{ID=2});
arrSet.Add(new Order{ID=5});
Table<Customer> customers = DataContext.GetTable<Custo
LINQ Query Code:
from c in customers.AsQueryable()
join p in arrSet.Cast<Order>()
on c.ID.Value.ToString() equals p.ID.ToString()
select new { c.FirstName, c.LastName };
Error Message I am receiving:
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.
Can someone tell me whats going on?
Hi again,
The trick seems to be that you can not make a join between a Custom Class collection and a LINQToSQL query. Anyway, you can make it using a List(Of integer) so here's a suggestion for you :
1. Create a temporary query to hold every OrderId (as integer values) :
2. Using the correct syntax, make the query on customers using the CONTAINS statement on your list of OrderID...
I did it with VisualBasic and worked fine. Hope this helps!
The trick seems to be that you can not make a join between a Custom Class collection and a LINQToSQL query. Anyway, you can make it using a List(Of integer) so here's a suggestion for you :
1. Create a temporary query to hold every OrderId (as integer values) :
2. Using the correct syntax, make the query on customers using the CONTAINS statement on your list of OrderID...
I did it with VisualBasic and worked fine. Hope this helps!
qry = From c in customers.AsQueryable() _
Where ( From o In arrSet Select o.Id).ToList().Contains(c.Id) _
select new { c.FirstName, c.LastName };
ASKER
I finally figured out the tricky syntax...
Yes ? What is it ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would suggest you to do a join select like this :
qry = FROM c in Table, prov in ArrayProvider WHERE c.id = prov SELECT new { c.number, c.FName, c.LName };
I did this many times ( in vb.net) with Collection, ArrayList, Dictionnaries and it worked fine.
Hope this helps!