Create a Simple WCF service against Pubs Database using EF, LINQ – Part I

NOTE:-
  1. In this tutorial we create a simple WCF service which is used to retrieve and save data from Pubs Database (Microsoft sample database).
  2. The service also demos on how to use EF 4.0 and LINQ against database coding, with no requirement of writing SQL queries.
  3. The continuation of this tutorial shows on how to create a client (say a WebForm in a website) to utilize this service.
Get Started :-
  1. Open VS 2010
  2. File -> New -> Project
  3. Select Visual C# -> WCF (also select .net framework 4.0) -> select WCF Service Application Template.
  4. Give Name -> samplePubsService (also give the preferred location to save the solution).
  5. Click OK.
Initial Preparation of the Project in the solution:-
  1. Delete IService1.cs and Service1.svc, which we get by default at the time of project creation.
  2. Download Microsoft Sample Database – PUBS from HERE, else we can create our own database for operation.
  3. Extract the Database, Open the SQL file (.sql) of Pubs database -> instpubs.sql in the VS 2010, once opened -> Right click anywhere on the text zone and select -> Execute SQL (this creates our sample database).
  4. Now in Server Explorer (view –> server explorer) of VS 2010, Right Click -> Data Connections, Select -> Add Connection.
  5. In the open Dialog, Select –> ServerName (typically SQLEXPRESS), and also Select -> Database Name from the DropDownList (pubs).
  6. CLick OK. With this, the database part of the project is ready.
Adding a Service:-
  1. Right Click the project folder in Solution Explorer of VS 2010, select -> Add new Item, select -> Web, select -> WCF Service Template.
  2. Name it -> PubsService.svc
Adding a DataContract:-
  1. Right Click the project, select -> Add new Item, select -> web, select -> Class Template.
  2. Name it -> Author.cs

Place the below code in it:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace samplePubsService
{
    [DataContract]
    public class Author
    {
        [DataMember]
        public string authorId { get; set; }
        
        [DataMember]
        public string authorLName { get; set; }

        [DataMember]
        public string authorFName { get; set; }

        [DataMember]
        public string authorCity { get; set; }
    }
}

Similarly add one more DataContract, with name bookByAuthor.cs and place the following code in it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace samplePubsService
{
    [DataContract]
    public class bookByAuthor
    {
        [DataMember]
        public string titleId { get; set; }

        [DataMember]
        public string title { get; set; }

        [DataMember]
        public string type { get; set; }

        [DataMember]
        public DateTime pubdate { get; set; }
    }
}
Adding a ServiceContract:-

 

Open IPubsService.cs, and place the following code:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace samplePubsService
{
    [ServiceContract]
    public interface IPubsService
    {
        [OperationContract]
        Author GetAuthorById(string authorId);

        [OperationContract]
        List<bookByAuthor> GetBookOfAuthor(Author authorDetails);

        [OperationContract]
        void SaveBookDetails(List<bookByAuthor> booksDetails);

        [OperationContract]
        List<Author> GetAllAuthors();
    }
}

Implement the IPubsService interface in the PubsService.svc.cs:-

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace samplePubsService
{
    public class PubsService : IPubsService
    {
        public Author GetAuthorById(string authorId)
        {
            return null;
        }

        public List<bookByAuthor> GetBookOfAuthor(Author authorDetails)
        {
            return null;
        }

        public void SaveBookDetails(List<bookByAuthor> booksDetails)
        {

        }

        public List<Author> GetAllAuthors()
        {           
            return null;
        }
    }
}

To Simplify DataBase coding, we Use Entity Framework ORM module, the step by step process is described below –

  1. Right Click Project folder in Solution Explorer, Select -> Add new Item, Select -> Data, Select -> ADO.Net Entity Data Model, Name it -> PubsEntityModel.edmx
  2. Select -> Generate from Database, Select -> pubs.dbo in the DropDownList, also Check the checkbox to save entity ConnectionString to the Web.Config file.
  3. Check all tables; also check the two checkboxes at the bottom. (Checking the checkboxes of views and Stored Procedures is optional).
  4. Give name to the namespace -> pubsModel, Click Finish.

The complete model would be (Sorry!! for not getting the image quite clearly) –

 EFModel

 

But we are interested in only in the below entities (only for the sake of this tutorial) –

image 

This completes Entity Framework Part.

Now let’s code against pubsEntities i.e., the class generated from the Entity Framework (you can find it by double clicking the PubsEntityModel.Designer.cs). So let’s go back to PubsSevice.svc.cs:-

Import the name space System.Data.Entity to use the Entity models.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.Entity;

namespace samplePubsService
{
    public class PubsService : IPubsService
    {
        public Author GetAuthorById(string authorId)
        {
            using (pubsEntities pubs = new pubsEntities())
            {
                Author a = new Author();
                var author = (from p in pubs.authors
                              where p.au_id == authorId
                              select p).First();

                a.authorCity = author.city;
                a.authorFName = author.au_fname;
                a.authorId = author.au_id;
                a.authorLName = author.au_lname;

                return a;
            }
        }

        public List<bookByAuthor> GetBookOfAuthor(Author authorDetails)
        {
            List<bookByAuthor> books = new List<bookByAuthor>();
            using (pubsEntities pubs = new pubsEntities())
            {
                var titleIds = (from p in pubs.titleauthors
                                where p.au_id == authorDetails.authorId
                                select p);

                foreach(titleauthor titleId in titleIds)
                {
                    bookByAuthor book = new bookByAuthor();
                    var bookTitle = (from p in pubs.titles
                                  where p.title_id == titleId.title_id
                                  select p).First();
                    
                    book.title = bookTitle.titleOfBook;
                    book.type = bookTitle.type;
                    book.titleId = bookTitle.title_id;
                    book.pubdate = bookTitle.pubdate;

                    books.Add(book);
                }
            }
            return books;
        }

        public void SaveBookDetails(List<bookByAuthor> booksDetails)
        {
            using (pubsEntities pubs = new pubsEntities())
            {
                foreach (var detail in booksDetails)
                {
                    var book = (from p in pubs.titles
                               where p.title_id == detail.titleId
                               select p).First();

                    book.type = detail.type;
                    book.titleOfBook = detail.title;
                    book.pubdate = detail.pubdate;

                    pubs.SaveChanges();
                }
            }
        }

        public List<Author> GetAllAuthors()
        {
            List<Author> lstAuthors = new List<Author>();
            using (pubsEntities pubs = new pubsEntities())
            {
                
                var authors = from p in pubs.authors
                              select p;
                foreach (author singleAuthor in authors)
                {
                    Author a = new Author();
                    a.authorCity = singleAuthor.city;
                    a.authorFName = singleAuthor.au_fname;
                    a.authorLName = singleAuthor.au_lname;
                    a.authorId = singleAuthor.au_id;

                    lstAuthors.Add(a);
                }
            }
            return lstAuthors;
        }
    }
}
The folder Structure:-

image

To Test the PubsService.svc:-

 

Its Simple -> Press Ctrl + F5

A WCF Test client will open, now double click on any of the method on the left pane, and enter the input manually as shown below and then press Invoke. Results should be displayed in the Bottom Pane.

 Untitled

 

This Completes the coding part of the Service.

To Follow up with the Client construction, which utilizes this Service. Please Click the below Link – PART II.

Create a Simple WCF Service Client against Pubs Database using EF, LINQ – Part II

Disclaimer:

All coding and suggestions made in the above discussion is strictly in point of view of the author. It is neither mentioned any where nor even concluded that this is the only possible way to go with, as there will be always a chance for a new approach in achieving the same requirement (in context of programming). The above mentioned code is strictly written and explained for the sake of new comers to C# and ASP.NET world.

You may also like...

  • chakravarthy

    examples r good but lengthy, examples must be in simple

  • RamiVemula

    @chakravarthy

    Thanks for giving me nice comments. Yes, indeed examples are lengthy, but my main motive is to give freshers a detailed explanation about introductory concepts.

  • Kev

    Hi – Lets say this sample application is hosted and only available to those who log in to a site and gain access.

    If 100 people are logged in at once and are querying and updating the database, would we use the multiple concurrency to allow a new thread be created for each user?

  • Asad

    Wow!! Wonderful!! Its a crystal clear for novice and for all users also.
    Thank you so much

  • jo

    Excellent, there are a lot of tutorials out there but I find that you give just enough detail. This is a simple and complete example. Thank you.

  • William Beaty

    Cntl + F5 to start the WCF Test Client gives me an error says:

    Failed to add a service. Service metadata may not be accessible. Make sure your service is running and exposing metadata.

    Is there some special code needed in the webconfig?

    Please respond.

  • RamiVemula

    @William – IF you follow the exact same process as I have MEntioned in the the tutorial, there should not be any problem. What I was thinking is that your web.config is not exposing MEX endpoint which is MetaData endpoint. Please check that.

  • William Beaty

    Can you display what your web.config has please.

    This is what I have.

  • William Beaty

    It was a namespace issue. I have it working now.
    Thanks.

  • Sudheer Rishi

    Excellent, Great Place where i finally found what i need.

    Love to see more articles from you, in this crystal clear manner

    Thank You
    Sudheer Rishi
    +91 8801640530
    +91 9246655446

  • Stefan le Roux

    Excellent! The best, straight forward, WCF EF example if seen. Thanks a lot.

  • Cleyton Ferrari

    Thank you! I am one week studying WCF (theoretical part) and practice this part explained to me many things, thanks again for sharing that knowledge with us!

    Please keep writing great tutorials like this!

  • Jairo

    Is it necessary to always create a POCO to expose an Entity (an entity framework entity)?

  • Ahmed

    Excellent, Great Place where i finally found what i need.

  • HMS

    Kindly, Is it possible to have source code for above example

  • TAN TH

    Bravo, thanks for the great article, I learn from much it about EF and WCF Data Service, straight to the point, very good.

  • RamiVemula

    @Jairo – not exactly always.

  • Manoj

    Hi Ravi

    I need a code for a WCF service which can give me generic insert and select facility on single tables. The service shoud expose a method which accepts the tablename(string), and an array or hashtable which holds the values to be inserted in it. And, another method which accepts a tablename and a where condition based on what the select will be done. Is it possible to do it using generics+linq ? If yes please give me some lead on it.

    Regards
    Manoj

  • Mike Clarke

    I was unable to get part two to work but that’s not how I want to communicate with the WCF Service anyway so I didn’t bother trying to fix it.

    I have to say this was the best tutorial on the subject I could find on the NET and I had been looking for a while. The screen shots and code are exactly the way all tutorials should be laid out. Pat on the back and please continue to make great tutorials you make people happy!

    Cheers

    Mike.

  • TFM

    Can you show example of crud?

  • TFM

    This was great! Can you add crud to your example?

  • TFM

    Can you add crud to your example?

  • Oye

    Hi,
    Nice article. I am getting the same error below

    Cntl + F5 to start the WCF Test Client gives me an error says:

    Failed to add a service. Service metadata may not be accessible. Make sure your service is running and exposing metadata.

    Please how did William Beaty solve this problem?

  • Vaibhav

    Great example ……….. thats cool

  • Dave the Mechanic

    Amazing, way cool, and awesome job dude! I was scouring the web looking for a comprehensive example when I stumbled on this article. I followed steps outlined by Rami, my service compiled and worked just like the example showed. Rami; you did a great job – you deserve a medal or award for your article. Thanks

  • DavidS

    Excellent, I searched for a long time before I found this.

  • bakke

    great. many thanks