none
How can I convert below query from SQL to Linq RRS feed

  • Question

  • SELECT CF.CustomerProfileId,CF.Salutation,CF.Gender,CF.LastName,
               CF
    .DateOfBirth,AD.Line1,AD.Line2,AD.Line3,AD.Line4,AD.Line5,
               AD
    .Country,AD.ElectronicAddressDesc,NCIType.NationalCustomerIdentifierTypeDesc,
               NCI
    .NationalCustomerIdentifier from CustomerProfile CF
               
    left join Address AD on CF.CustomerProfileId = CF.CustomerProfileId
               
    left join NationalCustomerIdentifiers NCI on CF.CustomerProfileId = NCI.CustomerProfileId
               
    left join NationalCustomerIdentifierType NCIType on NCI.NationalCustomerIdentifierTypeId = NCIType.NationalCustomerIdentifierTypeId
             
    where CF.CustomerProfileId = @CustomerProfileid  and CF.Version = @Version
    Friday, October 21, 2011 8:24 AM

Answers

  • Hi PradeepKumarBN12345;

    The following query should give you what you need. Note that the CustomerProfileid and Version in the query are local variables assigned in the program.

    var results = from CF in DataContext.CustomerProfile
    		join AD in DataContext.Address on CF.CustomerProfileId equals CF.CustomerProfileId
    		join NCI in DataContext.NationalCustomerIdentifiers on CF.CustomerProfileId equals NCI.CustomerProfileId
    		join NCIType in DataContext.NationalCustomerIdentifierType on NCI.NationalCustomerIdentifierTypeId equals NCIType.NationalCustomerIdentifierTypeId
    		where CF.CustomerProfileId == CustomerProfileid && CF.Version = Version
    		select new {
    			CF.CustomerProfileId, 
    			CF.Salutation, 
    			CF.Gender, 
    			CF.LastName, 
    			CF.DateOfBirth,
    			AD.Line1, 
    			AD.Line2, 
    			AD.Line3, 
    			AD.Line4, 
    			AD.Line5, 
    			AD.Country,
    			AD.ElectronicAddressDesc, 
    			NCIType.NationalCustomerIdentifierTypeDesc, 
    			NCI.NationalCustomerIdentifier
    		};
    
     

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, October 21, 2011 1:48 PM
  • The solution above may not be correct. You must just grouping and DefaultIfEmpty like so

    var results = from CF in DataContext.CustomerProfile
      group join AD in DataContext.Address on CF.CustomerProfileId equals CF.CustomerProfileId Into CFGroup = Group

      from cfi in CFGroup.DefaultIfEmpty()
      group join NCI in DataContext.NationalCustomerIdentifiers on CF.CustomerProfileId equals NCI.CustomerProfileId Into NCIGroup=  Group

      From ccii in NCIGroup.DefaultIfEmpty()
      join NCIType in DataContext.NationalCustomerIdentifierType on NCI.NationalCustomerIdentifierTypeId equals NCIType.NationalCustomerIdentifierTypeId into NCITypeGroup = Group

    from ncit in NCITypeGroup.DefaultIfEmpty
      where CF.CustomerProfileId == CustomerProfileid && CF.Version = Version
    select new {
       CFGroup.CustomerProfileId, 
       CFGroup.Salutation, 
       CFGroup.Gender, 
       CFGroup.LastName, 
       CFGroup.DateOfBirth,
       CFGroup.Line1, 
       CFGroup.Line2, 
       CFGroup.Line3, 
       CFGroup.Line4, 
       CFGroup.Line5, 
       CFGroup.Country,
       CFGroup.ElectronicAddressDesc, 
       NCITypeGroup .NationalCustomerIdentifierTypeDesc, 
       NCITypeGroup .NationalCustomerIdentifier
    };

    or something very close to that.


    TheBugSlayer
    Friday, October 21, 2011 2:26 PM

All replies

  • Hi PradeepKumarBN12345;

    The following query should give you what you need. Note that the CustomerProfileid and Version in the query are local variables assigned in the program.

    var results = from CF in DataContext.CustomerProfile
    		join AD in DataContext.Address on CF.CustomerProfileId equals CF.CustomerProfileId
    		join NCI in DataContext.NationalCustomerIdentifiers on CF.CustomerProfileId equals NCI.CustomerProfileId
    		join NCIType in DataContext.NationalCustomerIdentifierType on NCI.NationalCustomerIdentifierTypeId equals NCIType.NationalCustomerIdentifierTypeId
    		where CF.CustomerProfileId == CustomerProfileid && CF.Version = Version
    		select new {
    			CF.CustomerProfileId, 
    			CF.Salutation, 
    			CF.Gender, 
    			CF.LastName, 
    			CF.DateOfBirth,
    			AD.Line1, 
    			AD.Line2, 
    			AD.Line3, 
    			AD.Line4, 
    			AD.Line5, 
    			AD.Country,
    			AD.ElectronicAddressDesc, 
    			NCIType.NationalCustomerIdentifierTypeDesc, 
    			NCI.NationalCustomerIdentifier
    		};
    
     

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, October 21, 2011 1:48 PM
  • The solution above may not be correct. You must just grouping and DefaultIfEmpty like so

    var results = from CF in DataContext.CustomerProfile
      group join AD in DataContext.Address on CF.CustomerProfileId equals CF.CustomerProfileId Into CFGroup = Group

      from cfi in CFGroup.DefaultIfEmpty()
      group join NCI in DataContext.NationalCustomerIdentifiers on CF.CustomerProfileId equals NCI.CustomerProfileId Into NCIGroup=  Group

      From ccii in NCIGroup.DefaultIfEmpty()
      join NCIType in DataContext.NationalCustomerIdentifierType on NCI.NationalCustomerIdentifierTypeId equals NCIType.NationalCustomerIdentifierTypeId into NCITypeGroup = Group

    from ncit in NCITypeGroup.DefaultIfEmpty
      where CF.CustomerProfileId == CustomerProfileid && CF.Version = Version
    select new {
       CFGroup.CustomerProfileId, 
       CFGroup.Salutation, 
       CFGroup.Gender, 
       CFGroup.LastName, 
       CFGroup.DateOfBirth,
       CFGroup.Line1, 
       CFGroup.Line2, 
       CFGroup.Line3, 
       CFGroup.Line4, 
       CFGroup.Line5, 
       CFGroup.Country,
       CFGroup.ElectronicAddressDesc, 
       NCITypeGroup .NationalCustomerIdentifierTypeDesc, 
       NCITypeGroup .NationalCustomerIdentifier
    };

    or something very close to that.


    TheBugSlayer
    Friday, October 21, 2011 2:26 PM