Inheritance in SQL Server and LINQ?

Question

The way I usually create my applications is by creating the database first and using LINQ to SQL to create the classes for me. But in my current project I want to try and use inheritance, and I don't think it's possible creating inheritance in a database?

Thus I'm wondering how I would create an inheriting class with SQL Server / LINQ, so I can save / get data from my database. For example if I have The parent:

- contact (id, name, telephone number, address, email..)

And the children:

- customer(id, title, activity, discount, vat...)
- supplier (id, title, activity, vat, bankaccount_number, manager, expiration_day...)
- driver (id, first name, hire_date, fire_date, function, date of birth...)

How would I create a working database connection for these classes? I don't have a lot of experience with creating classes as I usually let Visual Studio (LINQ) create them for me.

I'd greatly appreciate any help, Thanks :)

qxn · Accepted Answer · 2012-02-27 17:40:31Z

1

Linq-to-Entities (Entity Framework) provides this functionality out of the box.

You can design your entities as being derivations of a parent class, and then create queries for them like so:

var customers = context.ContactSet.OfType<Customer>();
var suppliers = context.ContacctSet.OfType<Supplier>();
var drivers = context.ContactSet.OfType<Driver>();

The parent class and children classes share an entity key. The database should have a (1:0 or 1) relationship between the parent table and child tables.

I don't think anything similar exists for Linq-to-SQL.

answered Feb 27, 2012 at 17:40

qxn

17.6k3 gold badges55 silver badges72 bronze badges

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

4 Comments

Schoof Over a year ago

I'm not sure I understand, can you give a more broad example? Thanks

Schoof Over a year ago

Is this what the database should look like? (klant = customer) i.imgur.com/48FBr.png

qxn Over a year ago

If 'customer' extends 'contact', then the customer_id should be a foreign key relationship to contact_id, as well as the primary key for the customer table. Here's a good walkthrough on the topic: robbagby.com/entity-framework/…

Schoof Over a year ago

Thanks for the help, that walktrough is really helpful too. :)

Peter Kiss · Accepted Answer · 2012-02-27 18:29:00Z

1

I would create another table: ContactTypes than i would set a type for each contact. After this i could create an extra table: PropertiesToContactTypes and after this a new one with name: PropertiesToContacts. I think this is a pretty simple and usefull approach.

If you only create things in the program code you will find your self in hard situations when you have to deal with complicated queries. Linq to SQL is a great tool but for complicated queries you should use stored procedures.

edited Feb 27, 2012 at 18:29

answered Feb 27, 2012 at 17:40

Peter Kiss

9,3292 gold badges25 silver badges38 bronze badges

2 Comments

Schoof Over a year ago

I'm not sure what you trying to tell me here. Sorry!

Peter Kiss Over a year ago

ContactTypes (ID, ContactTypeName) Contact(anything, ContactTypeID[ref to ContactTypes]) Properties(ID, PropertyName) PropertiesToContectTypes(ContectTypeID, PropertyID) // only for edit page PropertiesToContacts(ContactID, PropertyID) I'm bad at english so it's hard to me to explain all of this so I'm asking you to try set up a database structure like the example i've provided.

Collectives™ on Stack Overflow

Inheritance in SQL Server and LINQ?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related