How do you use a linq to sql join two tables and return the result?

Question

I have been looking on Stack overflow but none of the answers seem to fully sort out this problem. The error I'm getting is:

Cannot initialize type 'mvcTest.Models.MakeModelSpec' with a collection initializer because it does not implement 'System.Collections.IEnumerable'

I have created a new type like this to get over the anonymous type error

public class MakeModelSpec 
{
    public int MakeId { get; set; }
    public string Name { get; set; }
} 

public IQueryable<MakeModelSpec> GetSpecs(int makeid)
        {

            var searchspec = from spec in db.Specs
                             join model in db.Models on spec.ModelID equals model.ModelID
                             join make in db.Makes on model.MakeID equals make.MakeId
                             select new MakeModelSpec
                             {
                                 make.MakeId,
                                 spec.Name,
                             };

        if (makeid != -1)
        {
            searchspec = searchspec.Where(make => make.MakeId == makeid);
        }
        return searchspec;
}

If anyone can point me in the right direction, I would be very grateful I'm using MVC c# and a sql server db

Answer 1

You're initializing your MakeModelSpec "with a collection initializer".

Collections such as List<Item> can be initialized as such

new List<Item>
{
    Item1,
    Item2
}

Your code is interpreted as trying to do that, as it does not supply the parameters, which are required for object initialization

new MakeModelSpec
{
   Make = make.MakeId,
   Name = spec.Name
}

Note that you can do object initialization without specifying parameter names if you're using anonymous types.

new
{
   make.MakeId,
   spec.Name
}

The only reason the above isn't interpreted as a collection initializer is that the type is anonymous, so that you're making up property names as you go. Therefore, the property names you're used to create the object can be inferred.

Hence, in the following example, Name = is redundant, because that property would be called Name anyway, but Id = is not redundant, because that causes the anonymous type to have a property called Id rather than MakeId which it would infer if you were not supplying a name:

new
{
   Id = make.MakeId,
   Name = spec.Name
}

If MakeModelSpec were to implement IEnumerable, so that it was actually a collection of something, then you would be able to do

new MakeModelSpec
{
    MakeModelSpec1,
    MakeModelSpec2
}

... provided that MakeModelSpec1 and -2 are of a type that MakeModelSpec#Add accepts. This is what the compiler thinks you are trying to do.