Tuesday 8 May 2012

Joins in Linq


using (EntityModel.Entities objEntities = new     
          EntityModel.Entities())
        {
            var stateList = objEntities.States;
            var cityList = objEntities.Cities;

            var InnerJoin = (from SL in stateList
                                   join CL in cityList on SL.StateID
                                   equals CL.StateID
                          select new
                          {
                               SL.StateID,
                               SL.StateName,
                               CL.CityID,
                               CL.CityName
                         });

            var LeftJoin = (from SL in stateList
                                  join CL in cityList on SL.StateID
                                  equals CL.StateID into CL_join
                                  from CL1 in CL_join.DefaultIfEmpty()
                                  select new
                                  {
                                      SL.StateName,
                                      cityID =(Int32?) CL1.CityID,
     // Casting if there is an error in result for Cast
                                     cityName = CL1.CityName
                                 });
           
            var RightJoin = (from CL in cityList
                                   join SL in stateList on CL.StateID
      equals SL.StateID into State_Join
                                   from SL1 in State_Join.DefaultIfEmpty()
                                   select new
                                    {
                                        stateID = SL1.StateID,
                                        CL.StateID,
                                        CL.CityID,
                                        CL.CityName
                                    });

No comments:

Post a Comment