LInq to sql query

Question

Hi,

I have a sql query as follows:

  Declare @DivisionNo INT

  SET @DivisionNo = 5117



  SELECT distinct CASE WHEN ISNULL([DivisionNo],'') <> @DivisionNo 
                       THEN @DivisionNo ELSE [DivisionNo] END as DivisionNo

      --,[RecordID]    
      ,[AcctCat]    
      ,[AcctCatDesc]    
      ,[CostCode]    
      ,[CostCodeDesc]

  FROM [dbo].[vw_eSchdl_AcctCat_CostCode]    
  WHERE DivisionNo = @DivisionNo

  UNION

  SELECT distinct CASE WHEN ISNULL([DivisionNo],'') <> @DivisionNo 
                       THEN @DivisionNo ELSE [DivisionNo] END as DivisionNo

      --,[RecordID]    
      ,[AcctCat]    
      ,[AcctCatDesc]    
      ,[CostCode]    
      ,[CostCodeDesc]

  FROM [dbo].[vw_eSchdl_AcctCat_CostCode]    
  WHERE AcctCat not in (
      SELECT [AcctCat]     
      FROM [dbo].[vw_eSchdl_AcctCat_CostCode]
      WHERE DivisionNo = @DivisionNo
  )

How can I duplicate it using linq to sql?

Thanks

Answer 1

You can convert from SQL to Linq using Linqer. You can download it from here.

Answer 2

There's no better way to learn than to do it yourself using linqpad if you have a bit of time. You can have a tab with the SQL open and a tab with LINQ and try to duplicate your query results.

Answer 3

How about functionally equivalent?

int divisionNo = 5117;

var matches = from ac in context.AcctCatCostCodes 
              where ac.DivisionNo == divisionNo
              select ac;

var missingAcctCat = from ac in matches
                     select ac.AcctCat;

var others = from ac in context.AcctCatCostCodes
             where !missingAcctCat.Contains(ac.AcctCat)
             select ac;

var union = from ac in matches.Union(others)
            select new
            {
                DivisionNo = ac.DivisionNo ?? divisionNo,
                ac.AcctCat,
                ac.AcctCatDesc,
                ac.CostCode,
                ac.CostCodeDesc
            };

... same as methods instead of query syntax ...

var matches = context.AcctCatCostCodes
                     .Where(ac => ac.DivisionNo == divisionNo);

var missingAcctCat = matches.Select(ac => ac.AcctCat);

var others = context.AcctCatCostCodes
                    .Where(ac => !missingAcctCat.Contains(ac.AcctCat));

var union = matches.Union(others).Select(ac =>
             new
            {
                DivisionNo = ac.DivisionNo ?? divisionNo,
                ac.AcctCat,
                ac.AcctCatDesc,
                ac.CostCode,
                ac.CostCodeDesc
            });

... SQL generated by LINQ2SQL ...

SELECT COALESCE([t4].[DivisionNo],@p2) AS [DivisionNo], 
       [t4].[AcctCat], 
       [t4].[AcctCatDesc], 
       [t4].[CostCode], 
       [t4].[CostCodeDesc]
FROM (
    SELECT [t3].[AcctCat], [t3].[AcctCatDesc], [t3].[CostCode], 
           [t3].[CostCodeDesc], [t3].[DivisionNo]
    FROM (
        SELECT [t0].[RecordID], [t0].[AcctCat], [t0].[AcctCatDesc], 
               [t0].[CostCode], [t0].[CostCodeDesc], [t0].[DivisionNo]
        FROM [AcctCatCostCode] AS [t0]
        WHERE [t0].[DivisionNo] = @p0
        UNION
        SELECT [t1].[RecordID], [t1].[AcctCat], [t1].[AcctCatDesc], 
               [t1].[CostCode], [t1].[CostCodeDesc], [t1].[DivisionNo]
        FROM [AcctCatCostCode] AS [t1]
        WHERE NOT (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [AcctCatCostCode] AS [t2]
            WHERE ([t2].[AcctCat] = [t1].[AcctCat]) 
                  AND ([t2].[DivisionNo] = (@p1))
            ))
        ) AS [t3]
    ) AS [t4]