0

I have a query that I've managed to get running in SQL Server but I can't get running in LINQ. The SQL table has multiple joins but there's one table that's not a join that includes data and I think that's where my LINQ query fails.

The code compiles fine in Visual Studio but it crashes when the query is run. Here is my SQL:

SELECT DISTINCT TOP (1000) 
MA_MASTER.YEAR_ID,
PC_PARCEL.P_ID, 
PC_PARCEL.TAX_MAP, 
PC_PARCEL.LEGAL_INDEX,
ISNULL(NULLIF(PC_ADDRESS.FORMATED_ADDRESS, ''), 'Unknown Address') AS FORMATED_ADDRESS,
OWNR.NA_ID, 
NA_NAMES.NAME_INDEX,
MA_MASTER.INSP_FLAG,
IIF(MA_MASTER.INSP_FLAG IS NULL, NULL, 
  (SELECT TOP 1 [GOVERNPORTALDEV].[dbo].[VT_USER].[LONG_DESC_EN] 
  FROM [GOVERNPORTALDEV].[dbo].[VT_USER] 
  WHERE MA_MASTER.INSP_FLAG = [GOVERNPORTALDEV].[dbo].[VT_USER].[CODE] 
  AND TABLE_NAME like '%ins_flag%')) AS [LONG_DESC_EN]
FROM ((GOVERNPORTALDEV.dbo.PC_PARCEL 
INNER JOIN GOVERNPORTALDEV.dbo.PC_ADDRESS
  ON GOVERNPORTALDEV.dbo.PC_PARCEL.P_ID = 
GOVERNPORTALDEV.dbo.PC_ADDRESS.P_ID) 
INNER JOIN (SELECT P_ID, Min(NA_ID) AS NA_ID FROM 
GOVERNPORTALDEV.dbo.PC_OWNER GROUP BY P_ID) AS OWNR
  ON GOVERNPORTALDEV.dbo.PC_PARCEL.P_ID = OWNR.P_ID) 
INNER JOIN GOVERNPORTALDEV.dbo.NA_NAMES 
  ON OWNR.NA_ID = GOVERNPORTALDEV.dbo.NA_NAMES.NA_ID
INNER JOIN GOVERNPORTALDEV.dbo.MA_MASTER
  ON GOVERNPORTALDEV.dbo.MA_MASTER.P_ID = GOVERNPORTALDEV.dbo.PC_PARCEL.P_ID
,[GOVERNPORTALDEV].[dbo].[VT_USER] 
WHERE [GOVERNPORTALDEV].[dbo].[PC_PARCEL].[JURISDICTION] ='MANKO' 
  AND MA_MASTER.YEAR_ID = 2018
  AND TABLE_NAME like '%ins_flag%'
  AND FROZEN_ID = 0
ORDER BY P_ID;

Here is my LINQ query:

var query = from pc_parcel in Context.PC_PARCEL
    join pc_address in Context.PC_ADDRESS 
      on pc_parcel.P_ID equals pc_address.P_ID
    join ownr in Context.PC_OWNER.GroupBy(pid => new { pid.P_ID, pid.NA_ID })
      .Select(ownr => new { ownr.Key.P_ID, NA_ID = ownr.Min(m => m.NA_ID) })
                      on pc_parcel.P_ID equals ownr.P_ID
    join na_names in Context.NA_NAMES
      on ownr.NA_ID equals na_names.NA_ID
    join ma_master in Context.MA_MASTER
      on pc_parcel.P_ID equals ma_master.P_ID
    join vt_user in Context.VT_USER
      on ma_master.INSP_FLAG equals vt_user.CODE

    where pc_parcel.JURISDICTION == juris_code && 
      ma_master.YEAR_ID == year && 
      ma_master.FROZEN_ID == 0 && 
      vt_user.TABLE_NAME.Contains("ins_flag")
    orderby pc_parcel.P_ID

    select new MaintenanceListDetail
    {
        AssessmentID = pc_parcel.TAX_MAP,
        LegalLandDescription = pc_parcel.LEGAL_INDEX,
        CivicAddress = pc_address.FORMATED_ADDRESS,
        ReasonForInspection = null,
        OwnerName = na_names.NAME_INDEX,
        OwnerEmail = null,
        OwnerPhone = null,
        Status = 0
    };

I used the question Creating a LINQ select from multiple tables as a basis for my code but I can't get it to work.

Nse
  • 305
  • 4
  • 21
  • 1
    You should really be using a `CROSS JOIN` to `[GOVERNPORTALDEV].[dbo].[VT_USER]` in your T-SQL query, not mixing up ANSI-89 and ANSI-92 `JOIN` syntax. – Thom A Oct 31 '18 at 12:51
  • If translating the query to `linq` turns out that difficult, are you sure that its a good idea to do so ? When you need to do maintenance on it will be difficult again and again... – GuidoG Oct 31 '18 at 13:04
  • @GuidoG I need to return the query as an IQueryable object because it's part of a repository. Is there an alternative? – Nse Oct 31 '18 at 16:28
  • No I am afraid there is not. The positive of this is that when you're done you will have learned `linq` very well. – GuidoG Oct 31 '18 at 16:38
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you with some pointers? – NetMage Oct 31 '18 at 18:56
  • What does "it crashes" mean exactly? – NetMage Oct 31 '18 at 18:57

0 Answers0