Mapping Data To Non-Table Entites In .NET

.NET
1

Hi, I’ve got a problem I’ve been going round and round on, and every time I think I have a solution I seem to come back to a core requirement not being fulfilled (I will detail this later).

What I’m trying to do, at a simplistic level, is to return a collection of objects from data supplied by a stored procedure. The object hierarchy is a follows

Shipment Object - this has a ShipmentNumber string property and a List<OrderHeader> collection

OrderHeader Object - this has OrderNumber double, OrderType string, ShipmentNumber string properties and a List<OrderLine> collection

OrderLine Object - this had OrderNumber double, OrderType string, OrderLine double, ItemNumber string properties

Shipment <-> OrderHeader is a 1 to many based on ShipmentNumber on either object

OrderNumber <-> OrderLine is a 1 to many based on OrderNumber and OrderType on either object

This object structure visualises how I need to view my data for a particular report (it’s actually a lot more complex than this, but this is a simple example I am initially trying to get working) and bears no resemblence to my database table structure. I am trying to map data from a stored procedure into this structure so that I end up with a collection of shipments, which then have a collection of order headers within them and these have a collection of lines within them, but I don’t seem to be able to do this. I can’t seem to do it with LINQ so I am looking at the Entity Framework. It seems that you can set an SP to return several SELECT outputs and then use the ObjectContext.Translate<…>(…) method to map data into objects, like this, and then the Translate method will take care of applying the associations, but when I trying this I’m getting an error reporting that the entity object properties aren’t mapped. Looking into this mapping it seems to come back to the objects having to refer to actual database tables (this is my recurring problem).

How, if it is possible, can I load this data, from a stored procedure(s), into this structure of objects using LINQ, EF, or something else without having to base my classes on database tables? Surely this is possible? I can do this with a single collection of objects, but the problem seems to come when these objects contain collections of other objects.

Just a few background points:- I have to use stored procedures to get the data - this is out of my control and won’t change. The tables concerned are spread across several database servers, some of which the web server physically doesn’t have access to, but the database server it does have access to has cross-server querying access to all the servers (hence another reason to use SPs as, AFAIK, LINQ, etc can’t do cross-server joins) so if I do pull table definitions from my development environment (where, obviously, I do have access to all the tables) these won’t be accessible in the production environment.

I could either create a flattened dataset of all the data then read through it, manually detecting the grouping changes (Shipment Number, OrderNumber) and then write my code accordingly, but then this doesn’t make use of OOP and seems a large step backwards, plus it doesn’t supply a reusable object for other, future, requirements. Instead, I could write code to go through the datasets and load the data manually into these objects but I figure why reinvent the wheel - surely this isn’t an unusual requirement and there must be a way to do this?

Thanks - MH

2

I know is not the anwser exatcly for your question but it will solve your problem faster. Use Dapper.Net in 5 minutes you can move on.

3

Unless I missed something, I can’t see that Dapper allows the creation of collections of objects that contain collections of objects within them - it allows structured object mapping (as does entity, linq, etc) like mapping firstname and lastname to User.Name.First and User.Name.Last but that isn’t what I want to do, I need to be able to translate:-

Shipment-1, Order-1, Line-1
Shipment-1, Order-1, Line-2
Shipment-1, Order-2, Line-1
Shipment-2, Order-3, Line-1

to a collection of objects like

Shipment 1 -- Order 1 -- Line 1
           |          |- Line 2
           |
           |- Order 2 -- Line 1

Shipment 2 -- Order 3 -- Line 1
4

Question I would ask here is how much is this structure going to change? It sounds complex enough that there are messes of dependencies so you are looking at a fixed target. In that case, I would just manually map out of ADO.NET bits. It is kind of ugly, but it is typically written once and forgotten once it works. LINQ solves the tricky part one used to have – querying the object graph after deserialization. Presuming you don’t have very, very large entities you can fly off linq in-memory queries alone. If you need a bit more persistent state, I’d check into pulling from the ugly ADO.NET mess into a local document DB – I like RavenDb, but there are other valid choices. Should give you the best of both worlds.

5

There shouldn’t be masses of data coming back, at the most complex it’s probably only about 300-400 records from about 10 tables across 3 databases, and once defined for a particular report it should be static. You talk about “In that case, I would just manually map out of ADO.NET bits” - by this, do you mean creating a non-database related structure of objects then manually creating them from datasets? If so then that’s always been my last resort, but I figured there must be a way to do this better and more automated. Also, that way means iterating through the data twice - once to load it then again to use it (although, I’m guessing that would happen anyway with LINQ, etc). Or do you mean something else?

6

Exactly what I meant. I do agree that it sounds a bit ugly, but it is a really decent choice if things aren’t changing alot beneath it. ORMs do this anyhow, so you won’t be reading it twice. Moreover, because you know what the data looks like, you can make your read significantly more efficent depending on how you want to do things. I’m actually seeing more and more people eschewing ORMs and building stuff this way believe it or not.

7

That’s what I’ve been coming round to what I’ll have to do, hehe - I’m just surprised that it doesn’t already handle it, as (as I said before) I can’t believe that this is an unsual requirement, especially in these days of “data visualisation”.

I had another suggestion from another forum that I create dummy tables in my database to reflect my data-view objects and then map stored procedures to these - the theory was that if I only used these for mapping, rather than accessed directly, then this should be ok, but I’m rather dubious about this - any thoughts?

If I find a way around this that’s neater than manually loading (though, as you say, this may well end up being more efficient, anyway.

Thanks - MH