0

I have a SQL which have already run fine

SELECT TOP (1000) [RecordHistoryID]
      [Change]
      ,[WhatChanged]
      ,[ChangeByUserID]
      ,[ChangeDate],
      REPLACE(WhatChanged, DIC.SourceText, DIC.Translation) AS TranslatedText
  FROM [dbo].[RecordHistory] AS RH
  LEFT JOIN [dbo].Dictionary DIC ON RH.WhatChanged LIKE '%Activity:%' + DIC.SourceText + '%']

But I have no idea how to write it in LINQ VB.NET with Contains condition. Currently, here is my code so far

  From r In db.RecordHistories
    Join u In db.Users On r.ChangeByUserID Equals u.UserID
    Group Join dic In db.Dictionaries
    Into group4 = Group
    From grt In group4.DefaultIfEmpty
    Select r, TranslatedText = grt.Translate

If I add Where function like Group Join dic In db.Dictionaries.Where(Function(x) r.Contains(x.SourceText)) it will not recognize "r" table and if I try Group Join dic In db.Dictionaries ON r.WhatChanged.Contains(dic.SourceText) it will not valid either.

I can write it as a store but I will have to update a lot of logic. So is there any solutions if I want to group join on text contains condition ?

DarknessZX
  • 686
  • 3
  • 12
  • I am confused. If you have a working sql Select why do you want to use link? – Mary May 13 '21 at 07:35
  • @Mary The code which already running is LINQ and there are still a lot of complicated filter logic in that code. If I write it as a store procedure, I will have to bring those logic filter too. Currently, I work around by separate it by 2 query. I just want to know is there anyway to "group join" with "contain" like I mention in the question for vb.net linq. – DarknessZX May 13 '21 at 07:50
  • 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. Basically (left) `JOIN`s with non-equality conditions must be replaced with a cross-join and a `Where` condition. Also, a general `LIKE` must be replaced by a database specific (if available) `EF.Functions.Like` or (in your case) broken up into say `String.IndexOf` tests. Also, you need to say what LINQ are you using: LINQ to Objects / SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x? What database provider? – NetMage May 18 '21 at 19:43

0 Answers0