LINQ inheritance problem

[Leo Seccia]

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) tlkpCompanyTypeID
strDescription
dtmCreate
strCreateUser

an so on...

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).
eg.

Table: tlkpGeneric
strDescription
dtmCreate
strCreateUser

Table: tlkpColor
(PK) tlkpColorID

Table: tlkpCompanyType
(PK) tlkpCompanyTypeID

etc...

I've also tried other combinations (eg. overring properties in subclasses)

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.

Am I tackling this the right way?

I hope I am making sense, sorry if I am not. Please let me know if I need to
clarify anything.

Thank in advance,

Leo

[Frans Bouma [C# MVP]]

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) tlkpCompanyTypeID
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) tlkpCompanyTypeID

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
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]

Hello Frans,

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

"Frans Bouma [C# MVP]" <pe******************@xs4all.nlwrote in message
news:Od**************@TK2MSFTNGP02.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
strDescription
dtmCreate
strCreateUser

Table: tlkpCompanyType
(PK) tlkpCompanyTypeID
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 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
strDescription
dtmCreate
strCreateUser

Table: tlkpColor
(PK) tlkpColorID

Table: tlkpCompanyType
(PK) tlkpCompanyTypeID

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#)
------------------------------------------------------------------------

[Leo Seccia]

Hello everyone,

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

eg.
var gdi = dc.GetTable(theType).Cast<IDropDownItem>();
dropDownItems.DataSource = gdi.Where(a =a.bObsolete == false);

NB. Using GetTable(type) rather than GetTable<TEntity>() returns the weakly
typed version... Therefore casting is needed...
"Leo Seccia" <ls*****@msn.comwrote in message
news:D4**********************************@microsof t.com...

Hello Frans,

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

"Frans Bouma [C# MVP]" <pe******************@xs4all.nlwrote in message
news:Od**************@TK2MSFTNGP02.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
strDescription
dtmCreate
strCreateUser

Table: tlkpCompanyType
(PK) tlkpCompanyTypeID
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 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
strDescription
dtmCreate
strCreateUser

Table: tlkpColor
(PK) tlkpColorID

Table: tlkpCompanyType
(PK) tlkpCompanyTypeID

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#)
------------------------------------------------------------------------