Entity Framework Code First Linq MVC 3 Razor

Question

This is my model;

namespace AtAClick.Models
{
    public class LocalBusiness
    {
        public int ID { get; set; }

        public string Name { get; set; }
        public int CategoryID { get; set; }
        public string address { get; set; }
        public string desc { get; set; }
        public int phone { get; set; }
        public int mobile { get; set; }
        public string URL { get; set; }
        public string email { get; set; }
        public string facebook { get; set; }
        public string twitter { get; set; }

        public string ImageUrl { get; set; }

        public virtual Category Category { get; set; }

    }

    public class Category
    {
        public int CategoryID { get; set; }
        public string Name { get; set; }

        public virtual ICollection<LocalBusiness> Businesses { get; set; }
    }

}

I have a view which lists the categories and one which lists the businesses. I want another which list all the business in a certain category My question is, what would my Linq query in my controller look like? Something like..

var companyquery = from c in db.LocalBusiness
                   where c.Category = Category.Name
                   select c;

But obvioulsy this isn't working. I'm new to all this so thanks in advance for the help, and if you need anymore detail just ask. Thanks!!

My controller, is this what you mean?

 public ViewResult Browse(int id)
 {

            int categoryID = id;

            var companyquery = from c in db.LocalBusinesses
                               where c.Category.CategoryID == categoryID
                               select c;

            return View(companyquery);
 }

Answer 1

You're comparing a category object (c.Category) with a static field of the Category class (Category.Name). The static field does not exist and therefor this will not compile.

You should have a variable stating the specific category object, or at least an identifier (like the CategoryID). Name is not enough, since this property is not unique (it's possible to have two different categories with the same name).

So, let's say you obtain a specific CategoryID somehow, then your LINQ query would look like:

    // The category ID to filter.
    int categoryID = 42;

    // Retrieve all companies that are listed under the given category.
    var companyquery = from c in db.LocalBusiness
                       where c.Category.CategoryID = categoryID
                       select c;

Your categoryID could, for instance, be obtained from a query parameter (declare as a controller method parameter).

Answer 2

If you are filtering by category name

string categoryName = "foo";

var companyquery = from c in db.LocalBusiness
                   where c.Category.Name == categoryName
                   select c;

If you are filtering by category id

int categoryId = 1;

var companyquery = from c in db.LocalBusiness
                   where c.CategoryID == categoryId 
                   select c;

Answer 3

Since Category has a collection of Businesses you can do

IEnumerable<LocalBusiness> result = null;
var category = db.Category.SingleOrDefault(cat => cat.Name == "an cat");
if(category != null){
    result = category.Businesses;
}

return result;