0

I have two tables, one the main entity and the other a translation record for it in a specific language

Recipe

  • Id | unique identifier

RecipeTranslation

  • Id | unique identifier
  • RecipeId | unique identifier
  • LanguageCode | string
  • Name | String

Given an array of language codes, does anyone know how I would make a query, searching this translation table for any records that should exists (but don't). There should be one record for each recipe & language code

The only parameter I would like to pass into the SELECT query would be an array of language codes

The pseudo code would be something like

  • for each recipe
  • check a translation record exists for each language code
  • if it does not show me the recipe id and language code pair missing

This is my current solution

var result = from ingredient in _dbContext.Ingredients
             join translation in _dbContext.IngredientTranslations
               on new { IngredientId = ingredient.Id, LanguageCode = "uk" } equals new { translation.IngredientId, translation.LanguageCode }
               into ingredientTranslation
             from x in ingredientTranslation.DefaultIfEmpty()
             where x == null
             select new { IngredientId = ingredient.Id, LanguageCode = "uk" };

I am able to fetch per language code. so I am just looping through the language codes and running this to return me the records that do not exist

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
Matster2
  • 137
  • 1
  • 14
  • Where do the language codes come from? Another table? – Panagiotis Kanavos Jul 19 '23 at 18:04
  • They are a string array in c#, so I would like to pass them into this query. its the only piece of information I give it – Matster2 Jul 19 '23 at 18:11
  • Just thinking, is a good way to approach this, create a temp table with all the expected ids (recipe id and language code) then do a search on this recipe table to check if each exists, set a field like found 0/1. then return back the not found result? – Matster2 Jul 19 '23 at 18:14
  • What are you trying to do with that? – Fildor Jul 19 '23 at 18:20
  • given a list of strings (language codes) find the records that should exists in the recipe translations table, but don't Then I can deal with them in code :) – Matster2 Jul 19 '23 at 18:23
  • Yes, I was interested in that "deal with them" part. :) – Fildor Jul 19 '23 at 18:24
  • o haha. any that don't exist I will then add a processed record for and insert it into the database – Matster2 Jul 19 '23 at 18:25
  • Well, ... you could do a temp table, left join and select where e.g. the Name column is null... – Fildor Jul 19 '23 at 18:28

2 Answers2

1

This is easy to do in raw SQL with a left join and a table-valued parameter.

You need to create a Table type in the database (once only)

CREATE TYPE dbo.Codes AS TABLE  
    ( Code nvarchar(50) )

Now, in your sql, you need a cross join and a left outer join. The cross join creates a line for every possible combination of recipe and required language code. The left outer join then brings in a translation if there is one. So if the language code of the translation is null, then there's no translation.

I'm going to tell you to use QueryFirst because it's by far the nicest way to use TVPs from C#. You can also use dapper, or avoid the TVP altogether by constructing a table literal directly in you sql.

-- queryfirst
-- designTimeSection
declare @RequiredLanguageCodes Codes;
-- endDesignTimeSection

SELECT R.Id, RLC.Code
FROM Recipe R
cross join @RequiredLanguageCodes RLC
left outer join RecipeTranslations RT on R.Id = RT.RecipeId and RLC.Code = RT.LanguageCode
WHERE RT.LanguageCode is null

When you save that, you should see a generated execute method like the this...

public List<MyQfDto> Execute(IEnumerable<Codes> requiredLanguageCodes)
...

public class Codes{
    public System.String Code{get; set;}
}

Now you just feed the generated method a List of your desired language codes.

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110
0

If this is an ongoing need of your application, and you have a LanguageCode table, you could build a view, much like bbsimonbb outlined, called vwMissingTranslations. Then you can query it in your code with an array of LanguageCodes in a Where clause. This view could also be used for getting counts of all missing translations, or missing translations by Language, or by Recipe.

Adam The Nomad
  • 1