Linq to Entities, Master Detail into DataContracts Query

Question

This is part of my WebAPI, and I'm having trouble getting this data out of Linq to entities into my Datacontract objects. I will be returning a custom data type to the caller, whether they want it XML or JSON, I don't care, I'm just handing this to WebAPI to take care of it.

I'm using VS2013 and EF5, ASP.NET 4.5 in C#

The structure is as follows:

ProductCategory

ID
CategoryName
(a million other things)
List<Products>

---

Products

ID
ProductName
(a million other things)
Category

---

I have set up a DataContract that looks like the following:

ProductCategoryDataContract

ProductCategoryId
ProductCategoryName
List<ProductDataContract> Products

---

ProductDataContract

ProductName

---

Basically, I want to get a Linq query to return ALL categories, and within it ALL products.

from prodcat in context.ProductCategories order by prodcat.ItemOrder
select new ProductCategoryDataContract
{
ProductCategoryId = prodcat.Id
Products = prodcat.Products // this obviously fails as ProductDataContract != Products
}

If I try

Products = new List<ProductDataContract> { //initializer stuff }

I don't have any of the intellisense things I would think I would have (ProductName, etc), because I'm guessing I'm in the list.

Basically, I have all the relationships set up and I can get everything in straight up EF, but because I'm putting these into new datacontracts, it's giving me a little grief (mostly due to my lack of linq knowledge).

My question is: 1.) how can I do this

and 2.) how can I do this with minimal database hits. Potentially I'm firing off thousands of items within tens of product groups.

Thanks much, and if I'm not clear on anything, please lmk. And, the above is pseudocodish, not the real deal so if I made stupid naming errors, that's unlikely 'it' :)

Answer 1

public interface IProducts
{
    int ProductId { get; set; }
    decimal Price { get; set; }
    List<IProductCategories> Categorieses { get; set; }
}

public interface IProductCategories
{
    int ProductId { get; set; }
    string ProductCategoryName { get; set; }
}

internal class Products : IProducts
{
    public int ProductId { get; set; }
    public decimal Price { get; set; }
    public List<IProductCategories> Categorieses { get; set; }
}

internal class ProductCategories : IProductCategories
{
    public int ProductId { get; set; }
    public string ProductCategoryName { get; set; }

    public ProductCategories(int productId, string productCategoryName)
    {
        ProductId = productId;
        ProductCategoryName = productCategoryName;
    }
}

public class ProductDataContract
{
    public int ProductId { get; set; }
    public List<IProductCategories> Categorieses { get; set; }
}

//Here is how you get your data:

        // your retun objects
        var products = new List<ProductDataContract>();

        using (
            var db =
                new DataClassesDataContext(
                    ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString))
        {

            foreach (var prod in db.Products.Select(p => new Products {ProductId = p.ProductId}))
            {
                prod.Categorieses = new List<IProductCategories>();

                foreach (var category in db.ProductCategories.Where(c => c.ProductId == prod.ProductId))
                {
                    prod.Categorieses.Add(new ProductCategories(category.ProductId, category.ProductCategoryName));
                }

                products.Add(new ProductDataContract {Categorieses = prod.Categorieses, ProductId = prod.ProductId});
            }
        }