loads of useful information, examples and tutorials pertaining to web development utilizing asp.net, c#, vb, css, xhtml, javascript, sql, xml, ajax and everything else...

 



Advertise Here
C-Sharpener.com - Programming is Easy!  Learn Asp.Net & C# in just days, Guaranteed!

Getting started with Linq-To-Entities tutorial

by naspinski 1/6/2009 4:05:00 AM

The transition from Linq-to-SQL to .Net's Entities framework is incredibly simple

In my humble opinion, Linq is easily the greatest thing .Net has come out with in the past few years, and along with it, Linq-to-SQL (L2S) was a godsend. Being quick to hop on the L2S bandwagon, I quickly became a huge fan of the framework, it is amazing easy and useful. That being as it is, I was quite disappointed when I heard that Linq-to-SQL was no longer going to be advanced at all. Now, it is surely not dead, it is still very usable and effective at what it does, but I like to stay with frameworks that will be actively advanced and fbug-fixed. So I decided to make the jump to Linq-to-Entities (L2E), and it was suprisingly simple.

This guide should be a good starting point for anyone whether or not they are familiar with L2S, and a quick 'jumping guide' to L2S developers. Here's how to get started:

Make your ADO.NET Entity Data Model (.edmx file)

This is comparable to the .dbml file that you made with L2S.
  • Right click on your App_Code folder and select New Item
  • Select ADO.NET Entity Data Model and name it (I left the default Model.edmx for the example)
  • Choose Generate from database and click Next
  • Choose your database from the dropdown and choose the name to save the ConnectionString as and click Next
  • Choose all the things you want included, I just chose tables, but you may want to include views and SPs if you have them
  • *Be sure to remember the Model Namespace you choose for your .edmx (I will use DatabaseModel for the example) - click Finish

Now it will take a moment to run through and produce your .edmx; when it is done you will see a nice little representation of your tables reminiscent of the .dbml display in L2S:

Access your .edmx

Now we simply access the .edmx much like we did with L2S. You must remember to add Using DatabaseModel (or whatever your namespace was) to the code where you are using it. Now we will make a DatabaseModel object and use it to add a 'product' to the database. First, you need to make your Entity Access Object:
DatabaseEntities db = new DatabaseEntities();

Then you can use it to enter an object:
products p = new products();
p.product_name = "Word Processing Software";
p.price = 99;
p.stock = 100;
db.AddToproducts(p);
db.SaveChanges();

Once again, if you are familiar with L2S, this is almost the same exact thing! If you are new, this is very straight-forward and should be easy to understand:
  • Make an object of a type inside your database
  • Fill the object up with data
  • Put it into the database
  • Save the changes

Now L2E one-ups L2S and makes data entry even easier, this will accomplish the same as above:
db.AddToproducts(products.Createproducts(0, "Accounting Software", 300, 125));
db.SaveChanges();

Notice that the 'product_id' is entered as '0', that is because it is auto-incrementing, so it doesn't matter what I put there, it will be ignored. Now I don't know about you, but that little bit there will save me hundreds of lines of code! I am starting to like L2E already!

Display your data

Now that we have put a few objects in to the database, we can go and check it out. Later we will dig in with code, but first we will use a LinqDatasource/GridView:
  • Drag a LinqDataSource (LDS) on to the page and click Configure Data Source from the little square on the upper right of the LDS
  • Choose your Object Model and click Next
  • Choose your table and click Finished
  • Drag a GridView (GV) on to your page
  • In the GV option box, choose your datasource from the dropdown

now just view your page:

Modify Data

Now that we have seen how to put in data, we will modify some; once again, L2E makes it trivially simple:
products edit = (from p in db.products where p.product_id == 2 select p).First();
edit.product_name = "Account Software v2";
db.SaveChanges();

The Linq statement is EXACTLY like it would be in L2S, and just like in L2S, you can shorten up this with some Lambda integration; this will accomplish the same thing:
db.products.First(p => p.product_id == 2).product_name = "Accounting Software v2.1";
db.SaveChanges();

Deleting Items
Now that we have seen Insert and Update, the next logical step is Delete, and it is just as easy. Let us delete the 'Word Processing Software' frorm the table:
products del = (from p in db.products where p.product_id == 1 select p).First();
db.DeleteObject(del);
db.SaveChanges();

And the same exact thing shorthand with Lambdas:
db.DeleteObject(db.products.First(p => p.product_id == 1));
db.SaveChanges();

Once again, I have to say I like the approach that L2E takes of that of L2S, as it is not necessary to specify which table to delete from, as an object can only be deleted frorm the table that it is in.

Using your database relations

As in L2S, L2E takes great advantage of well designed databases and relations. If you noticed up above, the two tables in my database have a 1 to many relation frorm product to reviews. Each element in the 'reviews' table is required to relate to an element in the 'products' table (column 'product_id'). Let's fill the DB with a few more products and some reviews; a lot is going to go on here:
products p1 = db.products.First(p => p.product_id == 2);
products p2 = products.Createproducts(0, "Strategy Game", 49, 99);
products p3 = products.Createproducts(0, "Sports Game", 39, 99);
db.AddToproducts(p2);
db.AddToproducts(p3);

reviews r1_1 = reviews.Createreviews(0, "Much Improved", "this is a much better version", "Bill Brasky");
r1_1.productsReference.Value = p1;
reviews r2_1 = reviews.Createreviews(0, "Terrible", "worthless", "Dirk Digler");
r2_1.productsReference.Value = p2;
reviews r3_1 = reviews.Createreviews(0, "Great Game", "very tough AI", "Wonderboy");
r3_1.productsReference.Value = p3;
reviews r3_2 = reviews.Createreviews(0, "Very Fun", "the Bears rule", "Mike Ditka");
r3_2.productsReference.Value = p3;

db.AddToreviews(r1_1);
db.AddToreviews(r2_1);
db.AddToreviews(r3_1);
db.AddToreviews(r3_2);

db.SaveChanges();

Now to explain what just happened:
  • The first line gets an object of type products where product_id == 2 (this is 'Accounting Software v2.1')
  • The next two lines create new products
  • The next two submit those into the database

Now we have a total of 3 objects in the 'products' table and none in the 'review' table, that is where the next 8 lines come in. If you break up those 8 lines into pairs of two, you can see they are all the same thing really:
  • Make a new object of type reviews
  • Assign its foreign key reference to a products object

Since the database requires a relation between these two tables, you must be sure to set the reference. The last lines just submit everything and commit them to the database.

Now that that is in there, you can use the real power of relations in L2E. Once again, it is almost the same as L2S:
foreach (products p in (from _p in db.products select _p))
{
  Response.Write("<h3>" + p.product_name + " - $" + p.price + "</h3>");
  Response.Write("<b>Reviews:</b><div style='border:1px solid navy;padding:5px;'>");
  p.reviews.Load(); // this loads all the reviews that relate to the product p
  foreach (reviews r in p.reviews)
    Response.Write("<b>" + r.title + " - " + r.reviewer + "</b><br />" + r.review + "<br />");
  Response.Write("</div><br />");
}

And this is what you get:
Yes, I know I used the ugly 'Response.Write', and the output is hideous... but this is just a demo people! As you can see, it is incredibly easy to iterate through all of the reviews of a products, this big difference being that you need to call the .Load() method or they will not be populated. Now this is both a blessing and a curse; this is very efficient as it only loads the related objects if need be, but... it is soemthing that is easy to forget and may leave you scratching your head. I think I like it.

Now relations also work the other way as well. Say you have a reviews object, and you want to know the price of the products it is related to. No need to actually look up the products object, you just call the relation:
reviews r = db.reviews.First(_r => _r.review_id == 3);
r.productsReference.Load();
Response.Write("Price: $" + r.productsReference.Value.price.ToString());

Once again, notice that you have to Load() the reference, or you will get nothing.

Now that should get you started. Like I said, if you are familiar with L2S, this transition should be no problem, and if you are new to this whole Linq arena, this should be simple to pick up. This new ADO is getting more and more impressive the more MS works on it. I can't even imagine goging back to the old methods...

Related posts

Comments

1/6/2009 5:10:34 AM

Cyril Gupta
Excellent Article! Loved it! It explains the concepts very nicely. I will take up on L2E Smile

Cyril Gupta in

2/2/2009 4:10:14 PM

Sam
Excellent! Right to the point. Thank you very much.

Sam us

3/11/2009 11:43:29 PM

StEpHeN
nice article...
i am planning to transit from L2S to L2E
thx for the effort

StEpHeN hk

4/6/2009 7:12:59 PM

Aaron
Great Article! I just started using L2S and I love it, so I was terrified when I heard that it was not going to be advanced anymore. Your article was exactly what I was looking for to explain the difference between L2S & L2E. I am no longer hesitant about L2E!

Aaron us

5/4/2009 6:35:42 PM

Moulde
Exactly what i was looking for!
Thanks alot!

Moulde dk

5/6/2009 8:48:39 PM

Rod
Oi...

I want to add a couple of things to your post (which is actually really good for starters).

As you already said, l2e provides the Create[Entity] method in every EntitySet collection created on the model. But creating related [parent-child] entities isn't really that exciting that way.

In you case, there is a more simpler way to achive this by using just the entity relationship from the parent entity as the following code points it out


//creating the instance, can be generated as well from the CreateProducts method. Not biggie

products p = new products(){ product_name = "my new prod", price = 100, stock = 10};

//Creating reviews

reviews r1 = new reviews(){ title = "making my point", review = "my review", reviewer = "rod" };

reviews r2 = new reviews(){ title = "And another thing", review = "Answer to the ultimate question is 42.", reviewer = "rod" };

p.reviews.add(r1);
p.reviews.add(r2);

db.AddProducts(p); //Just adding products to the collection. Everything else will be covered by linq framework.

//sending everything to persistance.
db.SaveChanges();


It will be more handy to relate objects directly through their instances than add it to the db model and then creating the reference between eachother.

Something even nicer is the fact that the insertion of the parent entity and its child definitions will be based on transactional context.

Cheers and thanks for the post.

Rod au

7/27/2009 11:49:17 AM

Richard
Thanks for a useful post.

Have you considered using an EntityDataSource instead of the LinqDataSource ?

Richard gb

7/28/2009 7:03:33 PM

frSurfer
Hi, Sorry to interfere byut as far as I'm concerned L2E is THE LINQ and L2SQL is one of its flavors.

Using L2SQL you can write:

dDim Query = From p in db.Products
Where p.Reviews.Reviewer = "Rod"
Select p.product_name, p.price,
p.Reviews.Title, p.Reviews.Review

DataGridView.DataSource = Query

Do you see "any" difference?

In order to ease a bit, I call a Foreign Key relationship an "Up Join" and a Entity Relation (the "One" side in One to Many) a "Down Join".

MMeaning that when you call Select p.Reviews.xxx
You are actually referring a group join.

frSurfer

8/5/2009 8:09:07 AM

Evert Wiesenekker
Thank you really helpfull!

Evert Wiesenekker nl


Comments are closed