0

Here is the Raw SQL it gives 4 distinct names as I have used the Distinct keyword

var strSql = "select distinct emp.PK_EmployeeID as 'PK_EmployeeID' , "
            + "(emp.First_Name+ ' ' + emp.Last_Name) as 'EmployeeName'  ,"
            + "  emp.IsActive as 'IsActive' , "
            + "  c.CompanyName   as 'LegalEntity' "
            + " from tSMR_Employee as emp left "
            + " outer join tClientCompany as c on c.ClientNumber=emp.FK_CMPClientID "
            + " left outer join tSMR_Employee_RoleAssign as era on era.FK_EmployeeID=emp.PK_EmployeeID "
            + " where emp.FK_CMPClientID=" + FK_CMPClientID + " and emp.IsActive=" + IsActive;

if (FK_ClientRole_TypeID != 0)
{
    strSql = strSql + "    and era.FK_ClientRole_TypeID=" + FK_ClientRole_TypeID;
}

var list = db.Database.SqlQuery<QueryResult>(strSql).ToList();

But when I tried to make its LINQ equivalent ... I am getting the common

Object reference not set

  var list = (
    from SMREmployee in db.tSMR_Employee.AsEnumerable()
    join ClientCompany in db.tClientCompany 
        on SMREmployee.FK_CMPClientID equals ClientCompany.ClientNumber
    join SMR_Employee_RoleAssign in db.tSMR_Employee_RoleAssign 
        on SMREmployee.PK_EmployeeID equals SMR_Employee_RoleAssign.FK_EmployeeID 
        into tmp_SMR_Employee_RoleAssign
    from lo_tmp_SMR_Employee_RoleAssign in tmp_SMR_Employee_RoleAssign.DefaultIfEmpty()

    where SMREmployee.IsDeleted == false 
    && SMREmployee.FK_SystemID == SystemID
    && (IsActive == 2 
        ? SMREmployee.IsActive == SMREmployee.IsActive 
        : IsActive == 1 
        ? SMREmployee.IsActive == true 
        : SMREmployee.IsActive == false)
    && (FK_CMPClientID == 0 
        ? SMREmployee.FK_CMPClientID == SMREmployee.FK_CMPClientID 
        : SMREmployee.FK_CMPClientID == FK_CMPClientID)
    && (FK_ClientRole_TypeID == 0 
        ? lo_tmp_SMR_Employee_RoleAssign.FK_ClientRole_TypeID == lo_tmp_SMR_Employee_RoleAssign.FK_ClientRole_TypeID 
        : lo_tmp_SMR_Employee_RoleAssign.FK_ClientRole_TypeID == FK_ClientRole_TypeID)

    select new
    {
        PK_EmployeeID = SMREmployee.PK_EmployeeID,
        IsActive = SMREmployee.IsActive,
        EmployeeName = Convert.ToString(SMREmployee.First_Name) 
            + " " 
            + Convert.ToString(SMREmployee.Middle_Name) 
            + " " 
            + Convert.ToString(SMREmployee.Last_Name),
        LegalEntity = ClientCompany.CompanyName
    }).OrderBy(t => t.PK_EmployeeID).Distinct();

error..... I guess I made some error in querying

Rohit Kumar
  • 1,777
  • 2
  • 13
  • 26
  • I know of LINQPAD...but I have never tried it..& I dont know how to use it either – Rohit Kumar Oct 26 '17 at 14:15
  • 2
    I see a left outer join. That means you expect some things to be `NULL`, but I don't see you checking for any `NULL`s in your LINQ query. Check for NULLs and handle them how you want... – oerkelens Oct 26 '17 at 14:16
  • yeah yeah.....exactly...so i need to check for null both sides ???? – Rohit Kumar Oct 26 '17 at 14:17
  • You need to check everything you expect can be null, and figure out what to do if something is null. – oerkelens Oct 26 '17 at 14:17
  • hmmmm...how about I make it a inner join first..& see if the code works fine...if it doesn't then may be we can check for left joins with those null handlers – Rohit Kumar Oct 26 '17 at 14:19
  • this is not a generic question...why did this event got marked as duplicate :( – Rohit Kumar Oct 26 '17 at 14:20
  • Why do you have `lo_tmp_SMR_Employee_RoleAssign.FK_ClientRole_TypeID == lo_tmp_SMR_Employee_RoleAssign.FK_ClientRole_TypeID` in the query? That should always be true. – juharr Oct 26 '17 at 14:21
  • @RohitasBehera Question about null reference exceptions are very common and often get closed as a duplicate. – juharr Oct 26 '17 at 14:23
  • because my input variable FK_ClientRole_TypeID is coming from the selection of a a dropdown... I am doing the above incase FK_ClientRole_TypeID =0 – Rohit Kumar Oct 26 '17 at 14:23
  • Your question is marked as a duplicate because you have not shown any effort in finding out _what_ in your whole bunch of difficult-to-read code _is_ actually `null`. And that is _exactly_ what happens multiple times per day: here's a lot of code, and something causes an NRE, please debug it for me. If you find the cause of the NRE, and you try to fix it, and that doesn't work, _then_ your question may become specific enough not to be seen as a duplicate. – oerkelens Oct 26 '17 at 14:23
  • @RohitasBehera That piece of code is comparing the same thing on both sides. It's pointless. – juharr Oct 26 '17 at 14:24
  • help me write a better null handler for **&& (FK_ClientRole_TypeID == 0 ? SMR_Employee_RoleAssign.FK_ClientRole_TypeID == SMR_Employee_RoleAssign.FK_ClientRole_TypeID : SMR_Employee_RoleAssign.FK_ClientRole_TypeID == FK_ClientRole_TypeID)** – Rohit Kumar Oct 26 '17 at 14:24
  • 2
    Also why are you doing `AsEnumerable`? That means it's pulling everything from `tSMR_Employee` and does some of the code in memory instead of converting to SQL which makes a big difference. – juharr Oct 26 '17 at 14:27
  • 1
    One final suggestion, if your joins are on foreign keys that are properly setup in EF then you should have navigation properties that you can use instead of writing out all of the complex joins. https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/ – juharr Oct 26 '17 at 14:30
  • yeah that was helpful – Rohit Kumar Oct 26 '17 at 14:31

0 Answers0