Sql – Linq to Sql Criteria in String Array

linq-to-sql

I have say three tables.

Projects

Id
Name

Categories

Id
Name

ProjectCategories

Id
ProjectId
CategoryId

I now have an array of strings which represents categories the user has elected to search on. I now need to find all the projects that have those category attached to them.

I tried;

string[] searchTerms = new string[2];
searchTerms[0] = "paint";
searchTerms[1] = "painting";

IQueryable<Project> projects = (from category in dc.ProjectCategories
where searchTerms.Any(val => category.Category.Name.Contains(val))
select category.Project).Distinct();

But I get the error;

{"Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator."}

Best Answer

Unless I'm mistaking your meaning, which is possible, I think you can just do a contains with the searchTerms

where searchTerms.Contains( category.Category.Name )

this will translate into SQL as

... WHERE Name IN ('paint','painting') ...

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – the syntax for an inner join in LINQ to SQL

It goes something like:

from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}

It would be nice to have sensible names and fields for your tables for a better example. :)

Update

I think for your query this might be more appropriate:

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

Since you are looking for the contacts, not the dealers.

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – the syntax for an inner join in LINQ to SQL

Related Topic