LINQ to SQL - Database Design issue

Question

I'm quite new to working on Databases in general, and hope to get your guidance as to what went wrong.

I have 4 tables at hand, currently set-up in this way:

enter image description here

Company - Just an ordinary Company table with certain data in it

Company_GoodsPackaging - A reference table for Company, where the information on WHAT kind of goods the company 'does' and what packaging type, for that good, is being done.

GoodsType - Basically an enum, values right now are:

1 Commodity

2 Food / feed

3 Live Animals

4 Plants

5 Special cargo

6 Exceptional

PackagingType - Also an enum, values:

1 Parcel

2 Pallet

3 Container

4 Bulk

5 Oversized

CODE

In my code-behind of my ASP.NET website I'm doing the following:

I iterate through all GoodsType values, and try to see whether CompanyX (a LINQ Company object) has this value in its table, and if so, what's the Packaging_Type.Description of the value.

The problem I'm having now, is that when I have my Company object, I don't seem to be able to extract its members.

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"
}

The relationships are done, everything seems to be correct, but I can't just seem to be able to extract the GoodsType and PackagingType of a Company_GoodsPackaging entry. I know it's a EntitySet.

Where is my DataBase design flawed/Code logic flawed? I have to say, I'm very new to working in DataBases alltogether. Any help/input would be much welcome.

Error I'm getting in VS is

Error 26 'System.Data.Linq.EntitySet' does not contain a definition for 'GoodsType' and no extension method 'GoodsType' accepting a first argument of type 'System.Data.Linq.EntitySet' could be found (are you missing a using directive or an assembly reference?) *****\Account\MyAccountTransport.aspx.cs 33 8

can you put all models involve for this design (code for models)? — Sampath
– Sampath, Commented Dec 1, 2012 at 10:38

Tim S · Accepted Answer · 2012-12-01 10:39:38Z

2

The problem is that curCompany.Company_GoodsPackagings is a collection of GoodsPackagings (hence the plural and the mention of System.Data.Linq.EntitySet). You can't therefore refer directly to a single GoodsType for the Company in this way.

I think you're looking for a way to test each one of the Company's GoodsPackagings 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")

answered Dec 1, 2012 at 10:39

Tim S

7277 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

beyond-code · Accepted Answer · 2012-12-01 10:58:28Z

2

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

Therefore the property Copmany_GoodsPackagings will be a collection and 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 suggest that you bring back the whole goods packaging object and then check to see if it has a value or not in order to determine boolean gvContainers:

You can then convert your anonymous type into a concrete class and add the 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 }
}

edited Dec 1, 2012 at 10:58

answered Dec 1, 2012 at 10:39

beyond-code

1,4221 gold badge12 silver badges20 bronze badges

4 Comments

KarolC Over a year ago

Hi there, thanks very much for your willingness to help. Indeed, Company -> Company_GoodsPackagings is a One to Many relationship, as one Company can have various entries in that table. However, using your solution, and Tim S's (THANKS AGAIN GUYS :)) I am now getting this error: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

beyond-code Over a year ago

Ahhh sorry, I guess the Any() function is not supported in linq to sql. I will modify my answer with an alternative solution...

KarolC Over a year ago

Roysvork, thanks. I'll try your solution soon. I'm ultimately using the results of this LINQ query to DataBind a GridView element on my webpage, but I guess it doesn't really matter. Just thinking how to map the right Column names to the new result. I'll let you know in due time. Thanks so much for your time and effort on this one. Still, something in the back of my head tells me, that this could be done easier (in terms of the database structure), or am I wrong? :) I like to question myself a lot..

beyond-code Over a year ago

No problems. You may also consider using entity framework instead of linq to sql if you are not too far along, as it supports more linq methods as per here: msdn.microsoft.com/en-us/library/bb738550.aspx

Collectives™ on Stack Overflow

LINQ to SQL - Database Design issue

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related