complex query using LINQ and C#

Answer 1

How come you are using a comma-delimited list? The better approach would be to have a table in between which stores the mapping between users and modules.

Answer 2

If you can, it might be best to refactor your tables rather than trying to deal with the data in the format it's in.

In your "usersModule" table, if you change the modules column to "moduleId" and just insert a record for every module associated with the userId, you'll have a much easier time querying the data I believe, and your database will be much easier to maintain.

Table: "modules"
moduleId    moduleName
1           Recommended...
2           Blah
3           ...

Table: "usersModule"
userId      moduleId
1           3
1           2
1           4

Answer 3

If it's an option, you might want to refactor your database schema. Your usersModule table should have one user id and one module id per row, and the moduleId column should be a foreign key to the module table. If you do it this way, the linq query would be trivial.

If you're stuck with the database as-is, you're probably stuck using two queries: one query to retrieve the module id's, and another query to retrieve the actual module info.

Answer 4

It would be something like this I think, but you haven't given enough exact information about your database structure for me to write the query exactly.

var q =
    from m in db.modules
    select new {
     m.moduleId,
     m.name,
     db.usersModules.Exists(um => um.userId == myUserId)
    };

Edit: oh I just realised you've got a comma-separated thing. Rearrange your database properly first, like the others have said.

Answer 5

Your query doesn't works because LinqToSQL tries to translate everything to plain SQL.

If you cannot refactor your schema, and use an intermediate table since you clearly have a many-to-many relation ship, you can do something like this:

var userModules = db.userModules.SingleOrDefault(m => m.userId == myUserId).modules;
// Get the modules of the user (a comma delimited string)
var integerModules = modules.Split(',').Select(m => int.Parse(m));
// Convert the comma delimited string to IEnumerable<int>

var query = db.modules.Select(x => new {
                x.moduleId,
                x.moduleName,
                isChecked = integerModules.Contains(x.moduleId)
}); // And finally do the query