Think beyond the 'mapping' - Entity (F6) and seeding data

[Phil Hepburn]

Hi Guys,

Yesterday I posted you about my recent issues with 'Foreign Key' constraints, while trying to 'Seed' my "Code First" database with some test data.

Well, after a good night's sleep on the problems, I have realised my mistake - yes, self inflicted pain !

Although in my developing applet on EF6 (and a 'Stock' "Code First" database) I did all the correct code stuff to make a suitable Entity 'mapping', I had not changed my 'head', or altered my mental approach. Yes, we have to THINK Entity 'mapping' as well as do it. Code is not enough. get the brain involved.

When it came to supplying some 'seeded' test data I mentally switched back into 'Table' mode, and wrote some stuff to fill Table rows, for Supplier and Customer and the likes. That is wrong, even if it can be done.

Once I came to my PC this morning and wrote some Entity data 'population methods', and applied them, everything went smoothly, no errors or issues at all. And the keys "under the hood" did everything for me. Foreign and Primary

Lets look at a way to use .NET code the EF way, to seed some data. Since we are in the code side of the entity mapping then we should do it the 'O' way and not the 'R' way.

Lets start with the top level code in image '_61' :-

PearlsEF6_61.jpg (125.12 KiB) Viewed 330 times

Lines 71 and 76 are each adding a new Order and each of these Orders is 3 'OrderLines'. The code is just test code so can be tidier etc. - we specify a Customer ID ( 1 or 2) and then a list of order lines.

Now lest look at the STATIC method to do the work - image '_62' :-

PearlsEF6_62.jpg (44.2 KiB) Viewed 330 times

The important lines are 40 and 47 where a LINQ static method is called to retrieve existing object data. The rest just get simple 'value type' data from the simple class object 'LineDetail' - quantity and product identity.

Here are the single retrieval methods :- [ we need these anyway, for other places, its a standard query really ...]

PearlsEF6_67.jpg (141.2 KiB) Viewed 330 times

And the simple data class is below - in image '_63' :-

PearlsEF6_63.jpg (37.64 KiB) Viewed 330 times

We had better see the results now - the longer order list for Customer 1 - image '_64' :-

PearlsEF6_64.jpg (176.75 KiB) Viewed 330 times

Yes, we could quickly and easily add many more Orders and OrderLines like this. Lets see the difference at the SQL database end - see image '_65' below :-

PearlsEF6_65.jpg (87.94 KiB) Viewed 330 times

Spot the before and after - or the table row way and the Entity 'save changes' way on filled Entity objects. All IDs have been assigned OK by EF6.

And this success is on top of the fact that there is now a more complex set of FKs (foreign keys) in the DB definition - see image '_66' :-

PearlsEF6_66.jpg (75.92 KiB) Viewed 330 times

Basically, we can do all of the data seeding without needing to look at SQL and 'Management Studio', as this whole new approach is .NET code based, and with objects, which we should be good at by now ;-0)

After a thorough check it seems as if EF6 has entered all the correct ID values in the correct places. Certainly this was a lot more rewarding, and less stressful than what I did yesterday, when I came at it from the 'Table' aspect. trick is to NOT mess with the ID properties at all, let EF6 have its head (own way).

So doing things this way, I could declare a new Customer, provide some details, and then add a bunch of Orders and OrderLines at the same time. We could even add a new Product and Supplier, if we were being really brave / silly / stupid. And at one 'db:SaveChanges()' all would be stored in the back-end DB and in the right places. EASY !

What to do now !? I need a nested drop list (combo box) in the WPF form so I can see the OrderLines within the Order, within the Customer.

Fingers crossed,
Phil.

P.S. Hope this interests a few - and remember, THINK Entities !!!
Like a lot of things these days, it easy when you know how.
I hope all the images get inserted in the correct places - Thumbs Held!

 

[Phil Hepburn]

Okay guys,

After being side-tracked by a couple of interesting issues / problems raised for discussion by Wolfgang, I have revisited my Entity data storage tasks.

Below is the visual result of me attempting to create a Customer entity and then providing new data values and objects whenever possible, in a chain or cascading manner.

Before I explain in detail what I mean by 'casscading' lets see how Micky Mouse came to my rescue :-

PearlsEF6_71.jpg (63 KiB) Viewed 330 times

In the code we need to discuss, I have not used any IDs for my new entries into various entities like Product and Supplier, Order or OrderLines - UNLESS the object already existed and had an Id already provided by the SQL engine.

I will explain the code seen below. The Customer is new and so no ID by me. The Order is new so I do not provide an ID. Now when we come to the bunch of four OrderLines I make I make four new lines, BUT, three are based on existing Products and therefore also Suppliers. I need to use an ID for these three Products.

The fourth new OrderLine is based on a new Product, which I then create, and so do not specify an ID, and that is true also for the new Supplier I create for this four OrderLine.

Lets now study the code to do this, take time and read it carefully :-

PearlsEF6_72.jpg (85.47 KiB) Viewed 330 times

The instantiation of new objects should be quite obvious and straight forward, BUT the FOREACH enumeration is for the code to find the existing Product objects from the database.

After the loop finds the first three Product objects and makes suitable lines, then on line 66 and 67 we create the fourth line from the new Product and Supplier.

Once we have the filled-out Customer object we simply add it to the Customers property of our DbContext object (EF6) and then save the changes.

And that is it, all the Key stuff is done for us 'under-the-hood' and rows are entered into 5 (five!) different tables in the SQL database. This is due to our Entity 'mapping'. Same as shown in previous posts.

Lets look at a couple of these to see the new rows :-

First the Supplier :-

PearlsEF6_73.jpg (62.07 KiB) Viewed 330 times

And now the Customer (look for Micky) :-

PearlsEF6_74.jpg (63.74 KiB) Viewed 330 times

And now the Product - check details with the hard wired .NET code :-

PearlsEF6_75.jpg (73.11 KiB) Viewed 330 times

And in case you, like me, get carried away with the instantiation details, and the right values to provide, look at the next image to see the line that MST be included - line 77.

Without line 77 all will work - every line of code, no compile errors etc., BUT, we will get no data being persisted to the DB Tables ;-0((

Here is the crucial line :-

PearlsEF6_76.jpg (39.89 KiB) Viewed 330 times

Okay then folks, so we stored some test data in the Entity way of 'thinking' and doing things.

Hope some of this is interesting, to at least some of you guys.  What we can say is that the X# compiler does work well, and LINQ is very useful indeed.

Seasonal Wishes,
Phil.
from Sunny Wales, UK.