CODEWITHSUNDEEP
c#linqlambda
Rob
Rob

Reputation: 638

Issue using Where method in LINQ

Consider this line of code:

List<SIDB_TransactionInformation> transaction = SIDB.SIDB_TransactionInformations
                    .Where(k => k.iscurrent == true & k.objectid == SIDB.func_GetObjectID("dbo.SIDB_Module")).ToList();

List<SIDB_Module> module = SIDB.SIDB_Modules
                    .Where(k => k.moduleid == transaction
                                                .Where(j => j.transactionid == k.moduleid)
                                                .SingleOrDefault().transactionid).ToList();

I do have 2 invocation of where method in different collection. First i distinct my list via iscurrent and objectid after that I do have other invocation of where method (for SIDB_Modules) to distinct the list via moduleid where in the the values refer to the transactionid of my previous list. Now i have an error message like this Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

sorry i'm new in lambda expression. need help badly

Upvotes: 0

Views: 85

Answers (2)

okrumnow
okrumnow

Reputation: 2416

Well, it looks like you're trying to do a join between SIDB_TransactionInformations and SIDB.SIDB_Modules. If so, try

var objectID = SIDB.func_GetObjectID("dbo.SIDB_Module");

List<SIDB_Module> modules = (from module in SIDB.SIDB_Modules
  join transaction in SIDB.SIDB_TransactionInformations on module.moduleid equals transaction.transactionid
  where transaction.iscurrent && transaction.objectid == objectID
  select module).ToList();

Upvotes: 0

Bryan Anderson
Bryan Anderson

Reputation: 16129

I think this is what you're looking for

List<SIDB_Module> module = SIDB
    .SIDB_Modules
    .Where(k => transaction.Any(j => j.transactionid == k.moduleid))
    .ToList();

Make a list of SIDB_Modules where there is a transaction whose transactionid is equal to the moduleid. LINQ to Sql might have an issue with Any, I don't remember, if it does you can rewrite it with an extra step like this

var transactionIds = transaction.Select(j => j.transactionid);
List<SIDB_Module> module = SIDB
    .SIDB_Modules
    .Where(k => transactionIds.Contains(k.moduleid))
    .ToList();

If performance is an issue you might consider going with the second method and putting transactionIds into something that implements ISet<T> and has a constant time lookup.

Upvotes: 1

Related Questions