CODEWITHSUNDEEP
sql-serverentity-frameworklinqjoinfilter
Zeeshan hack
Zeeshan hack

Reputation: 3

Asp.net MVC Lambda Syntax Basic of Joining and Contains

I have two tables

Products:

CREATE TABLE [dbo].[product] 
(
    [Id]       INT           IDENTITY (1, 1) NOT NULL,
    [p_name]   VARCHAR (50)  NOT NULL,
    [p_desc]   VARCHAR (250) NULL,
    [p_price]  VARCHAR (50)  NOT NULL,
    [p_colour] VARCHAR (70)  NULL,
    [p_cover]  VARCHAR (50)  NOT NULL,
    [p_front]  VARCHAR (50)  NOT NULL,
    [p_back]   VARCHAR (50)  NULL,
    [p_add1]   VARCHAR (50)  NULL,
    [p_add2]   VARCHAR (50)  NULL,
    [p_cat]    INT           NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [FK_product_catagory] 
        FOREIGN KEY ([p_cat]) REFERENCES [dbo].[catagory] ([Id])
)

And Catagory:

CREATE TABLE [dbo].[Catagory] 
(
    [Id]       INT          IDENTITY (1, 1) NOT NULL,
    [cat_name] VARCHAR (50) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
)

With the following sample data:

Product:

and Catagory:

I want to create an action method

public ActionResult ViewProducts(string Key)// contains category name
{
    var product = db.products.where(p => p.p_cat.contains(key));
    return View(product);
}

This method should return all products where category equals to key . e.g all products belongs to shirt category.

I know how to do this in SQL but lambda syntax is new for me.

Please help, thanks

Here is my model Click to view model

edo

Upvotes: 0

Views: 535

Answers (3)

Nilesh Sonkusare
Nilesh Sonkusare

Reputation: 195

Please change your Linq Query with the one below. 

var product = (from p in db.products
                join c in db.categoryies ON p.p_cat equals  c.Id
                 where c.cat_name.Contains(key)
                select p).ToList();

Note: Names of entities may vary as per your EF entity names.

Upvotes: 0

Ayaz
Ayaz

Reputation: 2121

Could you join Product(p_cat) and Category(Id) and filter out the records using Cateroty?

public ActionResult ViewProducts(string Key)// contains category name
    {
        var product = db.products
            .Join(db.categoryies, 
                p => p.p_cat,
                c => c.Id,
                (p,c) => new { Product = p, Category = c })
            .Where(x => x.Category.cat_name.Contains(key)).ToList();
        return View(product);
    }

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

var product = db.products.where(p => p.Categories.cat_name == key);

Names of members may vary depending on how EF has reverse-engineered names of your tables.

Upvotes: 1

Related Questions