LINQ to SQL - Database Design Problem

Question

I am completely new to working with databases in general and hope to get your guide on what went wrong.

I have 4 tables on hand currently configured this way:

Company . Just a regular company table with certain data.

Company_GoodsPackaging - a reference table for the Company, where information is made about what products the company makes and what type of packaging is suitable for this.

GoodsType . Basically listing, values ​​right now:

1 Item

2 Food / feed

3 live animals

4 plants

5 Special cargo

6 Exceptional

PackagingType . Also enumeration, values:

1 package

2 Pallet

3 container

4 Mass

5 Oversized

CODE

In my code for my ASP.NET site, I do the following:

I repeat all the GoodsType values ​​and try to find out if CompanyX (the LINQ company object) has this value in my table, and if so, what are Packaging_Type.Description values.

The problem that I am currently facing is that when I have an object of my company, I seem to be unable to extract its elements.

var theSource = (from g in Data.GoodsTypes select new { gvGoodsType = g.Description, gvParcels = true, gvContainers = curCompany.Company_GoodsPackagings.GoodsType == g.Goods_Type && curCompany.Company_GoodsPackagings.PackagingType1.Description == "Container" } 

Relations are fulfilled, everything seems to be correct, but I can’t just show that GoodsType and PackagingType are Company_GoodsPackaging records. I know this is an EntitySet.

Where is my database design wrong / code logic wrong? I have to say that I am very new to working in DataBase all together. Any help / input would be very welcome.

The error I get in VS is

Error 26 "System.Data.Linq.EntitySet" does not contain a definition for "GoodsType", and the extension method "GoodsType" cannot be found that takes the first argument of the type "System.Data.Linq.EntitySet" (do you have a using directive or assembly reference?) ***** \ Account \ MyAccountTransport.aspx.cs 33 8

Answer 1

If I'm not mistaken, Company → Company_GoodsPackagings is a one-to-many relationship?

Therefore, the Copmany_GoodsPackagings property will be a collection, so you will need to use the Any () function as follows:

 var theSource = (from g in Data.GoodsTypes select new { gvGoodsType = g.Description, gvParcels = true, gvContainers = curCompany.Company_GoodsPackagings.Any(gp => qp.GoodsType == g.Goods_Type && gp.PackagingType1.Description == "Container") } 

EDIT . It seems that my suggestion to use Any () is not supported by Linq to SQL. Therefore, I would advise you to return the entire product packaging object, and then check if it matters or not, in order to determine the logical gvContainers:

Then you can convert your anonymous type to a specific class and add a property at the end of the example:

 var theSource = (from g in Data.GoodsTypes select new GoodsResult { gvGoodsType = g.Description, gvParcels = true, gvGoodsPackaging_Container = curCompany.Company_GoodsPackagings.FirstOrDefault(gp => qp.GoodsType == g.Goods_Type && gp.PackagingType1.Description == "Container") } public bool gvContainers { get { return this.gvGoodsPackaging != null } } 

Answer 2

The problem is that curCompany.Company_GoodsPackagings is a collection of products (e.g. plural and mentioning System.Data.Linq.EntitySet ). Thus, you cannot directly refer to one type of GoodsType for the Company.

I think you're looking for a way to test each of the CompanyPackagings products against your conditions. To do this, you can use the Any() Linq extension method:

 using System.Linq; ... gvContainers = curCompany.Company_GoodsPackagings.Any(gp => gp.GoodsType == g.Goods_Type && gp.PackagingType1.Description == "Container")