Link to home
Start Free TrialLog in
Avatar of ImInOrlando
ImInOrlandoFlag for United States of America

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(arrProviders[i])                        
                                 select new { c.Number,
                                                       c.FName,
                                                       c.LName };
Avatar of CarlVerret
CarlVerret
Flag of Canada image

Hi,

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!
Avatar of ImInOrlando

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<Customer>();

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!


qry = From c in customers.AsQueryable() _
      Where ( From o In arrSet Select o.Id).ToList().Contains(c.Id) _
      select new { c.FirstName, c.LastName };

Open in new window

I finally figured out the tricky syntax...
Yes ?  What is it ?
ASKER CERTIFIED SOLUTION
Avatar of ImInOrlando
ImInOrlando
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial