LINQ inheritance problem

**Frans Bouma [C# MVP]** · Jun 27 '08, 07:49 PM

Re: LINQ inheritance problem

Leo Seccia wrote:

Hello everyone,
>
I have a c# project with a sql server database.
I have a number of lookup tables in my database which I successfully
managed to import into my LINQ dataclasses.
>
eg.
>
Table: tlkpColor
(PK) tlkpColorID
strDescription
dtmCreate
strCreateUser
>
Table: tlkpCompanyType
(PK) tlkpCompanyType ID
strDescription
dtmCreate
strCreateUser
>
an so on...

Tip: don't prefix table field names with type descriptions. Also don't
prefix table names with 'tbl' or other prefixes: The entity description
itself should be enough about what it represents.

I would like to create a generic lookup object to inherit from for my
lookup objects generated from the tables. I have tried to create a
generic object and setting discriminator property to implement
inheritance as seen on some tutorials on the net but I'm just not having
any luck (and I have spent a long time on this).

I assume you're talking about Linq to Sql. Linq to Sql only supports
inheritance on 1 table/view, thus the complete inheritance hierarchy is
mapped onto the same table/view.

That aside, I would advice against inheritance for this scenario, as
inheritance should only be used if you are adding specialization fields
to an entity. E.g. you have a base table 'Animal' and you have subtypes
'Dog', 'Fish' etc.

Lookup data is just that: a key and some textdata mostly, mostly
they're constant and there's NO inheritance involved at all,as the
subtypes aren't really extending the supertype.

eg.
>
Table: tlkpGeneric
strDescription
dtmCreate
strCreateUser
>
Table: tlkpColor
(PK) tlkpColorID
>
Table: tlkpCompanyType
(PK) tlkpCompanyType ID
>
etc...
>
I've also tried other combinations (eg. overring properties in subclasses)

this requires a table per type, which isn't supported by Linq to Sql.
It is by other o/r mappers though.

However, as I said above, it's not useful here. I understand that you
might think that the fields 'Description', 'CreateUser' and 'Create' are
shared among more than 1 type and therefore could benefit from
inheritance, but you also have to understand that if you have 1 table
per type, performance degrades because you have to join the two tables
together.

Essentially, I would like to union all my lookup tables into one generic
one with a composite primary key (id + type/discriminator value) but
using inheritance.

You shouldn't do this. Don't take this the wrong way, but you're making
a bunch of basic datamodel mistakes: why would you choose to create a
compound PK if 'id' is already unique?

An entity is identified by a unique attribute (field). This can be any
field in the entity itself or by an artificial one added for this
purpose, e.g. 'id'.

The discriminator field has no place in this PK, as it adds NOTHING for
the identification for the entity instance (table row): 'id' is enough.

Please read some basic relational modeling papers. I can recommend this
paper http://www.orm.net/pdf/ORMwhitePaper.pdf and other papers on

Object Role Modeling

http://www.orm.net

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

**Leo Seccia** · Jun 27 '08, 07:49 PM

Re: LINQ inheritance problem

Hello Frans,

First of all, I would like to thank you for the reply. Here are a few notes.

"Frans Bouma [C# MVP]" <perseus.usenet NOSPAM@xs4all.n lwrote in message
news:OdAOTYBsIH A.4492@TK2MSFTN GP02.phx.gbl...

Leo Seccia wrote:

>Hello everyone,
>>
>I have a c# project with a sql server database.
>I have a number of lookup tables in my database which I successfully
>managed to import into my LINQ dataclasses.
>>
>eg.
>>
>Table: tlkpColor
>(PK) tlkpColorID
>strDescripti on
>dtmCreate
>strCreateUse r
>>
>Table: tlkpCompanyType
>(PK) tlkpCompanyType ID
>strDescripti on
>dtmCreate
>strCreateUse r
>>
>an so on...

>
Tip: don't prefix table field names with type descriptions. Also don't
prefix table names with 'tbl' or other prefixes: The entity description
itself should be enough about what it represents.
>

I know exactly what you mean - I really don't like prefixes and I don't find
them useful.
Unfortunately this part is not up to me and the db already exist.

>I would like to create a generic lookup object to inherit from for my
>lookup objects generated from the tables. I have tried to create a
>generic object and setting discriminator property to implement
>inheritance as seen on some tutorials on the net but I'm just not having
>any luck (and I have spent a long time on this).

>
I assume you're talking about Linq to Sql. Linq to Sql only supports
inheritance on 1 table/view, thus the complete inheritance hierarchy is
mapped onto the same table/view.
>
That aside, I would advice against inheritance for this scenario, as
inheritance should only be used if you are adding specialization fields to
an entity. E.g. you have a base table 'Animal' and you have subtypes
'Dog', 'Fish' etc.
>
Lookup data is just that: a key and some textdata mostly, mostly they're
constant and there's NO inheritance involved at all,as the subtypes aren't
really extending the supertype.
>

I agree with you all the way here, inheritance is definately not going to
help in this scenario and it would be a very bad idea to us it.

>eg.
>>
>Table: tlkpGeneric
>strDescripti on
>dtmCreate
>strCreateUse r
>>
>Table: tlkpColor
>(PK) tlkpColorID
>>
>Table: tlkpCompanyType
>(PK) tlkpCompanyType ID
>>
>etc...
>>
>I've also tried other combinations (eg. overring properties in
>subclasses)

>
this requires a table per type, which isn't supported by Linq to Sql. It
is by other o/r mappers though.
>
However, as I said above, it's not useful here. I understand that you
might think that the fields 'Description', 'CreateUser' and 'Create' are
shared among more than 1 type and therefore could benefit from
inheritance, but you also have to understand that if you have 1 table per
type, performance degrades because you have to join the two tables
together.
>

>Essentially, I would like to union all my lookup tables into one generic
>one with a composite primary key (id + type/discriminator value) but
>using inheritance.

>
You shouldn't do this. Don't take this the wrong way, but you're making a
bunch of basic datamodel mistakes: why would you choose to create a
compound PK if 'id' is already unique?
>
An entity is identified by a unique attribute (field). This can be any
field in the entity itself or by an artificial one added for this purpose,
e.g. 'id'.
>
The discriminator field has no place in this PK, as it adds NOTHING for
the identification for the entity instance (table row): 'id' is enough.
>

The id in the union would not be unique anymore...
Anyway, you are right I shouldn't do this and I am not going to (for obvious
reasons).
I guess it was a really bad way to explain what I want to do.
Basically, I need a way to have a generic class to access all lookup data
access classes in a uniform way and as I am new to LINQ I overlooked at what
inheritance should actually be used for and I explained very badly what I
needed to achieve. Sorry.
Here is the full picture, from an higher level:
We have a very large set of lookup lists that we would like to view, filter,
edit, modify, delete records using one form and common code. Using weakly
typed objects is a bit of a nightmare (eg. from GetTable(Type)) so we
thought, if we have one generic object to cast to and perform linq
operations against, it would be great.
And this is how it all started...
I am sorry if the post appears dumb, but it is our first real linq project
and I'm trying to figure out how to do things.

Please read some basic relational modeling papers. I can recommend this
paper http://www.orm.net/pdf/ORMwhitePaper.pdf and other papers on

Object Role Modeling

http://www.orm.net

>
FB
>
--

Thank you for the links, definately interesting...

Regards,

LS

------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

**Leo Seccia** · Jun 27 '08, 07:49 PM

Re: LINQ inheritance problem

Hello everyone,

I finally solved my problem... All I needed was an interface. ;-)
It works!!!

eg.
var gdi = dc.GetTable(the Type).Cast<IDro pDownItem>();
dropDownItems.D ataSource = gdi.Where(a =a.bObsolete == false);

NB. Using GetTable(type) rather than GetTable<TEntit y>() returns the weakly
typed version... Therefore casting is needed...

"Leo Seccia" <lseccia@msn.co mwrote in message
news:D46D14DF-4C8B-4950-AD78-6FF0CCFD8AB5@mi crosoft.com...

Hello Frans,
>
First of all, I would like to thank you for the reply. Here are a few
notes.
>
"Frans Bouma [C# MVP]" <perseus.usenet NOSPAM@xs4all.n lwrote in message
news:OdAOTYBsIH A.4492@TK2MSFTN GP02.phx.gbl...

>Leo Seccia wrote:

>>Hello everyone,
>>>
>>I have a c# project with a sql server database.
>>I have a number of lookup tables in my database which I successfully
>>managed to import into my LINQ dataclasses.
>>>
>>eg.
>>>
>>Table: tlkpColor
>>(PK) tlkpColorID
>>strDescriptio n
>>dtmCreate
>>strCreateUs er
>>>
>>Table: tlkpCompanyType
>>(PK) tlkpCompanyType ID
>>strDescriptio n
>>dtmCreate
>>strCreateUs er
>>>
>>an so on...

>>
>Tip: don't prefix table field names with type descriptions. Also don't
>prefix table names with 'tbl' or other prefixes: The entity description
>itself should be enough about what it represents.
>>

>
I know exactly what you mean - I really don't like prefixes and I don't
find them useful.
Unfortunately this part is not up to me and the db already exist.
>

>>I would like to create a generic lookup object to inherit from for my
>>lookup objects generated from the tables. I have tried to create a
>>generic object and setting discriminator property to implement
>>inheritance as seen on some tutorials on the net but I'm just not having
>>any luck (and I have spent a long time on this).

>>
>I assume you're talking about Linq to Sql. Linq to Sql only supports
>inheritance on 1 table/view, thus the complete inheritance hierarchy is
>mapped onto the same table/view.
>>
>That aside, I would advice against inheritance for this scenario, as
>inheritance should only be used if you are adding specialization fields
>to an entity. E.g. you have a base table 'Animal' and you have subtypes
>'Dog', 'Fish' etc.
>>
>Lookup data is just that: a key and some textdata mostly, mostly they're
>constant and there's NO inheritance involved at all,as the subtypes
>aren't really extending the supertype.
>>

>
I agree with you all the way here, inheritance is definately not going to
help in this scenario and it would be a very bad idea to us it.
>

>>eg.
>>>
>>Table: tlkpGeneric
>>strDescriptio n
>>dtmCreate
>>strCreateUs er
>>>
>>Table: tlkpColor
>>(PK) tlkpColorID
>>>
>>Table: tlkpCompanyType
>>(PK) tlkpCompanyType ID
>>>
>>etc...
>>>
>>I've also tried other combinations (eg. overring properties in
>>subclasses)

>>
>this requires a table per type, which isn't supported by Linq to Sql. It
>is by other o/r mappers though.
>>
>However, as I said above, it's not useful here. I understand that you
>might think that the fields 'Description', 'CreateUser' and 'Create' are
>shared among more than 1 type and therefore could benefit from
>inheritance, but you also have to understand that if you have 1 table per
>type, performance degrades because you have to join the two tables
>together.
>>

>>Essentially , I would like to union all my lookup tables into one generic
>>one with a composite primary key (id + type/discriminator value) but
>>using inheritance.

>>
>You shouldn't do this. Don't take this the wrong way, but you're making a
>bunch of basic datamodel mistakes: why would you choose to create a
>compound PK if 'id' is already unique?
>>
>An entity is identified by a unique attribute (field). This can be any
>field in the entity itself or by an artificial one added for this
>purpose, e.g. 'id'.
>>
>The discriminator field has no place in this PK, as it adds NOTHING for
>the identification for the entity instance (table row): 'id' is enough.
>>

>
The id in the union would not be unique anymore...
Anyway, you are right I shouldn't do this and I am not going to (for
obvious reasons).
I guess it was a really bad way to explain what I want to do.
Basically, I need a way to have a generic class to access all lookup data
access classes in a uniform way and as I am new to LINQ I overlooked at
what inheritance should actually be used for and I explained very badly
what I needed to achieve. Sorry.
Here is the full picture, from an higher level:
We have a very large set of lookup lists that we would like to view,
filter, edit, modify, delete records using one form and common code. Using
weakly typed objects is a bit of a nightmare (eg. from GetTable(Type)) so
we thought, if we have one generic object to cast to and perform linq
operations against, it would be great.
And this is how it all started...
I am sorry if the post appears dumb, but it is our first real linq project
and I'm trying to figure out how to do things.
>

>Please read some basic relational modeling papers. I can recommend this
>paper http://www.orm.net/pdf/ORMwhitePaper.pdf and other papers on
>http://www.orm.net
>>
>FB
>>
>--

>
Thank you for the links, definately interesting...
>
Regards,
>
LS
>

>------------------------------------------------------------------------
>Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
>LLBLGen Pro website: http://www.llblgen.com
>My .NET blog: http://weblogs.asp.net/fbouma
>Microsoft MVP (C#)
>------------------------------------------------------------------------

>

LINQ inheritance problem

LINQ inheritance problem

Comment

Comment

Comment