CODEWITHSUNDEEP
asp.net-mvcasp.net-mvc-3linq
Smithy
Smithy

Reputation: 2190

Search ranking in LINQ, MVC 3 and Entity Framework

Previously in SQL I would use something like this to rank my search results:

        --search product name
    SELECT tblProduct.ProductID, 20 AS Ranking
    FROM tblProduct
    INNER JOIN tblManufacturer ON tblProduct.ManufacturerID=tblManufacturer.ManufacturerID
    LEFT OUTER JOIN tblProductCollection ON tblProduct.CollectionID=tblProductCollection.CollectionID
    WHERE tblManufacturer.Name + ISNULL(' ' + tblProductCollection.CollectionName, '') + ' ' + tblProduct.Name LIKE '%' + @term + '%'  AND tblProduct.Active = 1
    UNION ALL

    --search product exact name
    SELECT tblProduct.ProductID, 200 AS Ranking
    FROM tblProduct WHERE Name = '%' + term  AND tblProduct.Active = 1
    UNION ALL

This example says if your search term is contained in the name: 20 is the rank, if you match the name exactly: 200 is the rank. Union the tables together, order by Ranking(descending) and hey presto!

I'm trying to do this in LINQ this time round and am unsure how to go about doing it, to be honest I'm unsure my previous example was the best way to do it originally.

So, I have a Product Entity mapped to my database and I've added a property in my partial class called SearchRanking:

            var query = from p in db.Products
                    where p.Name.Contains(term)
                    select p;

        var query2 = from p in db.Products
                     where p.Name.ToLower() == term
                     select p;

Somehow I need to set the properties like so:

            var query = from p in db.Products
                    where p.Name.Contains(term)
                    select p, p.SearchRanking = 20;

        var query2 = from p in db.Products
                     where p.Name.ToLower() == term
                     select p, p.SearchRanking = 200;

Am I on the right track?

Upvotes: 0

Views: 1088

Answers (2)

Mark Oreta
Mark Oreta

Reputation: 10416

If you're wanting to create a new anonymous type you could do this:

 var foundProducts = (from p in products
                                 where p.Name.Contains(term)
                                 select new Product  
                                     {
                                         ProductId = p.ProductId,
                                         Category =  p.Category,
                                         Brand =  p.Brand,
                                         SearchRanking = p.Name.ToLower() == term ? 200 : 20
                                     }).OrderBy(s => s.SearchRanking).Take(20);

Upvotes: 1

saj
saj

Reputation: 4806

I would do something like this;

        var query = (from p in db.Products
                    where p.Name.Contains(term)
                    select p).ToList().ForEach(p => p.SearchRanking = 20);

A more efficient way would be to;

        var query = (from p in db.Products
                    where p.Name.Contains(term)
                    select new Product
                    {
                        Id = p.Id,
                        //set the other props here
                        SearchRanking = 20
                    }).ToList();

Upvotes: 1

Related Questions