CODEWITHSUNDEEP
c#asp.netlinq-to-sql
Jaj
Jaj

Reputation: 98

subqueries in Linq to Sql

I M stuck in linq query where i m searching database & showing result using

DBSearchDataContext db = new DBSearchDataContext();
        object q = from b in db.Products
                   where b.ProductCode.Contains(val) |
                   b.ProductName.Contains(val) |
                   b.Specification.Contains(val) |
                   b.Description.Contains(val) |                       
                   b.Category.Contains(val) 
                   select b;

      GridView1.DataSource = q;
     GridView1.DataBind();

i can not display category name from category table where categoryID matches.

I do this in sql like this

how to do this in linq

SELECT ID, ProductCode, DisplayOrder, ProductName, imgThumb, inStock, Status, Amount, (SELECT Category FROM Category AS aaa WHERE (Products.CategoryID = CategoryID)) AS Category FROM Products ORDER BY ID DESC

help me

Upvotes: 2

Views: 1064

Answers (2)

Jaroslav Jandek
Jaroslav Jandek

Reputation: 9563

If you are missing some mapping, why not use a simple join?

var q = from p in db.Products
        join c in db.Category on p.CategoryID equals c.CategoryID
        where ...
        select new
        {
          p.ProductCode,
          ...,
          c.Category
        };

or with proper mappings:

var q = from p in db.Products
        where ...
        select new
        {
          p.ProductCode,
          ...,
          p.Category.Category
        };

Upvotes: 1

grizzly
grizzly

Reputation: 1156

You can put the subquery into the Linq LET clause. See

for example.

Upvotes: 0

Related Questions