Difference between two Entity Set using LINQ

In this post I am going to discuss about how to get the difference between two table/ entity sets For Ex. we want to get rows form table_A which are not exists in table_B. (When table_A primarykey is part of table_B foreignkey).

Following is way to get the row(s) form table_A which are not part of table_B

SELECT  
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

In Linq to Sql you can achieve same thing just by calling one function Except.

var infoQuery =
    (from tblA in db.Table_A
    select tblA.Key)
    .Except
        (from tblB in db.Table_B
        select tblB.Key);

So by using above query you can easily achieve task of getting difference between set of collection.

CodeProject

Find duplicate with Linq

In this small post I am going to discuss about how to get list of duplicate items for the collection that we do in sql.
For example I have to get list of the email id which is get entered in user table more than one time.

SELECT email, 
  COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Linq query work same as on the set of collection and which make use of count

DataClassesDataContext db = new DataClassesDataContext();
var duplicates = db.Users
    .GroupBy(i => i.emailid)
    .Where(g => g.Count() > 1)
    .Select(g => g.emailid);
foreach (var d in duplicates)
    Console.WriteLine(d);

So by using above query you can easily achieve task of finding duplicate for your set of collection.
CodeProject

Sql Where In with Linq

In my current project I am using Linq To Sql ORM as my database layer for doing database operation. Now as I am moving further I got requirement to that I have to filter one table record form another table. For example I have to get list of employee which are either ProjectManger or TeamLead.

Sql query to for this is

select * 
from Employee
where deptid in (select deptid from Department)

To do same thing in Linq To Sql you need to use Contains() on one set of record function as you can see below

DataClassesDataContext db = new DataClassesDataContext();
List>p;int< lstDept = db.Department.Select(x => x.Dept_PKEY).ToList>p;int<();

var qry = from emp in db.Employees
where lstDept.Contains(item.Dept_PKEY)
select emp;

Above example shows how easily you can achieve WHERE IN functionality. Same you can achieve when you are coding using the set of collection object. Enjoy Linq...

CodeProject

Create REST service with WCF and Consume using jQuery

Code
What is REST ?
Rest=Representational state transfer which is an architecture design to represent the resources. Rest provide the uniform interface through additional constraints around how to identify resources, how to manipulate resources through representations, and how to include metadata that make messages self-describing.
Rest is not tied with any platform and technology but WEB is only platform which satisfy the all constrain. So any thing you build using Rest constrain, you do it on Web using HTTP.

Following are the HTTP methods which are use full when creating Rest Services.


HTTP Mehtods

• GET - Requests a specific representation of a resource
• PUT - Create or update a ersoure with the supplied representation
• DELETE - Deletes the specified resource
• POST - Submits data to be processed by the identified resource
• HEAD - Similar to GET but only retrieves headers and not the body
• OPTIONS - Returns the methods supported by the identified resource

In this article I am going discuss how to design and consume REST web service using WCF framework of .net service. As Example I am going to create  REST service which create, update and select Employee. Now start following the steps given below.

Designing of the URI
To designing Rest services you need to design URI, so for that you need to list out the resources that going to be exposed by the service.  As I am going to design Employee Create,updae and select service, so resource is

• Employee

Now to be more specific towards the operation

• List of Employees
• A Employee by Id
• Create Employee
• Update Employee
• Delete Employee

For example service is hosted on on local IIS server  and URL to locate it is
        http://localhost/restservices/MyRestServices.svc
In below discussion I am going to design the URL presentation for each operation

• List  Employees 

         URL is to get list of all employee
               http://localhost/restservices/MyRestServices.svc/Employees
         But there are no. of Employee in system and to apply filter on Employees
              http://localhost/restservices/MyRestServices.svc/Employees?type = {type}
         Note : In above url get list of employee of the type specified {type} of url.
         To Get single Emplyee by using uniqid of Employee.
              http://localhost/restservices/MyRestServices.svc/Employees/{EmployeesID}
         Note : In above URL {EmployeesID} is get replaced by ID of Employee.
         Extension to above scenario, If want to get employee by using id with the type
              http://localhost/restservices/MyRestServices.svc/Employees/{EmployeesID}?type = {type}

• Create Employee

          URL is to create employee is
             http://localhost/restservices/MyRestServices.svc/Employees/{employeeid}
         Note : In above URL {EmployeesID} is get replaced by ID of Employee.

• Update Employee

          URL to update employee is
             http://localhost/restservices/MyRestServices.svc/Employees/{employeeid}
         Note : In above URL {EmployeesID} is get replaced by ID of Employee.

• Delete Employee

         URL to delete employee is
             http://localhost/restservices/MyRestServices.svc/Employees/{EmployeeID}
        Note : In above URL {EmployeesID} is get replaced by ID of Employee.

As you can see here the URL for Create, Update and Delete are same but it makes actual difference when you combile this URL with specific HTTP methods.

Now Combine the url(s) with the HTTP methods

List Employees

• GET - http://localhost/restservices/MyRestServices.svc/Employees

Create/Update  Employee

• PUT - http://localhost/restservices/MyRestServices.svc/Employees/{employeeid}

Note : Both Create/Update employee use the same url but when you create employee employeeid =0 and when you update employee empoloyeeid=anynumeircvalue or GUID. This URL can be differ in you implementation this just example.

Delete Employee

• DELETE - http://localhost/restservices/MyRestServices.svc/Employees/{EmployeeID}

Designing WCF with REST
Article link to get understanding about WCF service : Create, Host(Self Hosting, IIS hosting) and Consume WCF servcie

To design Rest WCF service first start to design Class to represent Employee

[DataContract]
    public class Employee
    {
        [DataMember]
        public string Employeename { get; set; }
        [DataMember]
        public string Designation { get; set; }
        [DataMember]
        public string Address { get; set; }
        [DataMember]
        public string Email { get; set; }
    }

as you see Employee class DataContaract attribute so that is going to serve data to client i.e going to work as data exchanger.

Now start with Creation of WCF service which going to serve data to the client i.e going to work as REST full service.
You need to decorate each method in your Servercie interface with the WebInvoke attribute in addition to OperationContract, which allows user to bind method with UrlTemplate and HTTP method. Lets look the each method of interface one by one

public interface IService1
{

Get Methods
Following methods serve data which is requested using GET method of HTTP.

• GetEmployees

Allow to get all employees. "tag" allow user to filter data because some times system contains more no. of employee which increase traffice and slow down respose, so by specifing tag=manager or tag=senior you will get specific set of employee.

[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json,UriTemplate = "?tag={tag}")]
  [OperationContract]
  Employees GetEmployees(string tag);

• GetEmployee

Allow to get the employee with specific id. "Employee_id" of the uritemplate get replace with the employeeid.

[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json, UriTemplate = "Employees/{Employee_id}")]
  [OperationContract]
  Employee GetEmployee(string Employee_id);

Create and Update Method
Following method allow to insert new data and update existed using HTTP method PUT.

• PutEmployeeAccount

This method allow to insert new employee into system and to update existed employee. "Employee_Id" of the uritemplate get replaced by employee id if update is there and 0 if new employee require to create.

[WebInvoke(Method = "PUT", ResponseFormat = WebMessageFormat.Json, UriTemplate = "Employees/{Employee_Id}")]
  [OperationContract]
   string PutEmployeeAccount(Employee Employeeobj,string Employeename );    

Delete Mehtod
Method allow to remove data using HTTP method DELETE.

• DeleteEmployeeAccount

Method allow to delete employee from system. "Employee_Id" of the uritemplate get replace by the employee id.

[WebInvoke(Method = "DELETE", UriTemplate = "Employees/{Employee_Id}")]
  [OperationContract]
  void DeleteEmployeeAccount(string Employeename);
}

WebInvoke

• The WebInvokeAttribute determines what HTTP method that a service operation responds to I.e Http post, get or delete.
• Indicates a service operation is logically an invoke operation and that it can be called by the REST programming model.

UriTemplate

• An URI template is a URI-like String that contained variables marked of in braces ({, }), which provides a consistent way for building and parsing URIs based on patterns.
• UriTemplates are composed of a path and a query. A path consists of a series of segments delimited by a slash (/). Each segment can have a literal value, a variable value (written within curly braces [{ }] .query part is optional. If present, it specifies same way as querystring name/value pairs. Elements of the query expression can be presented as (?x=2) or variable pairs (?x={val}).
• One of the reason to add this class in .net is to support the REST architectural style used by developers today.

Consume WCF RESTfull service
Before you start following reading below its better to know that how to configure and consume WCF service using jQuery : Steps to Call WCF Service using jQuery

Below is jQuery code to consume web service.

var Type;
var Url;
var Data;
var ContentType;
var DataType;
var ProcessData;
var method;
//Generic function to call WCF  Service
function CallService() {
   $.ajax({
      type: Type, //GET or POST or PUT or DELETE verb
      url: Url, // Location of the service
      data: Data, //Data sent to server
      contentType: ContentType, // content type sent to server
      dataType: DataType, //Expected data format from server
      processdata: ProcessData, //True or False
      success: function(msg) {//On Successfull service call
       ServiceSucceeded(msg);
      },
   error: ServiceFailed// When Service call fails
  });
 }

function ServiceFailed(result) {
   alert('Service call failed: ' + result.status + '' + result.statusText);
   Type = null; 
   Url = null; 
   Data = null; 
   ContentType = null; 
   DataType = null; 
   ProcessData = null;
}

Code above declare and initializes variables which are defined above to make a call to the service. CallService function sent request to service by setting data in $.ajax
GetEmployee

function GetEmployee() {
   var uesrid = "1";
   Type = "POST";
   Url = "Service1.svc/Employees/1";
   ContentType = "application/json; charset=utf-8";
   DataType = "json"; ProcessData = false;
   method = "GetEmployee";
   CallService();
}

GetEmployee method used to get employee with specific id. URL to make call to service is
Service1.svc/Employees/1
in this 1 is employeeid that which is example in turn this function call the RESTfull service function which is having URLTemplate Employees/{Employee_Id} and method type is POST.
CreateEmployee

function CreateEmployee() {
   Type = "PUT";
   Url = "Service1.svc/Employees/0";

    var msg2 = { "Employeename": "Denny", "Designation": "engg.", "Address": "vadodara", "Email": "pr@ab.com" };

   Data = JSON.stringify(msg2);
   ContentType = "application/json; charset=utf-8";
   DataType = "json";
   ProcessData = true;
   method = "CreateEmployee";
   CallService();
}

this method used to create employee. URL to make call to service is
Service1.svc/Employees/Denny
in this 0 is employeeID (0 because I am creating new employee) that which is example in turn this function call the RESTfull service function which is having url template Employees/{Employee_Id} and method type is POST.
In the createemployee method I am creating Employee object with the property require which is going to be consume by service to create employee.

The following code checks the result.GetUserResult statement, so your result object gets the property your service method name + Result. Otherwise, it will give an error like object not found in Javascript.

function ServiceSucceeded(result) {
   if (DataType == "json") {
     if(method == "CreateEmployee")
     {
        alert(result);
     }
     else
     {
        resultObject = result.GetEmployeeResult;
        var string = result.Employeename + " \n " + result.Designation + " \n " +          result.Address + " \n " + result.Email;
        alert(string);
     }
             
   }         
}

function ServiceFailed(xhr) {
   alert(xhr.responseText);
   if (xhr.responseText) {
      var err = xhr.responseText;
      if (err)
          error(err);
      else
          error({ Message: "Unknown server error." })
   }
      return;
 }

$(document).ready(
         function() {
             CreateEmployee();
         }
);

So in this article I am just showed example of CreateEmployee and GetEmployee because this is enough to understand how to consume the method of WCF RESTfull service. Now can create other methods by downloading code and to understand it better way.

Summary
Its quite easy to Create RESTfull service using .net WCF framework. But it must require to understand how you represent your resource i.e design URL template to represent your resource on WEB.