3

We are using a .NET Core application (meanwhile running on localhost) and a SQL database running on SQL Server where we receive data from. In the following method which is called inside the controller we are using Entity Framework Core to query the data in SQL database:

    public ActionResult InspectionDetails(string inspectionId, string lang)
    {
        // load all checks for an inspection
        var inspectionChecks = (from ic in _context.InspectedChecks.Where(x => x.InspectionId.ToString() == inspectionId)
                                from cp in _context.CheckPoints.Where(x => x.CategoryId == ic.CategoryId && x.CheckId == ic.CheckId)
                                from tChecks in _context.Translations.Where(x => x.TranslationKey == cp.Title && x.Lang.ToLower() == lang.ToLower()).DefaultIfEmpty()
                                from tChecksFallBack in _context.Translations.Where(x => x.TranslationKey == cp.Title && x.Lang.ToLower() == "en").DefaultIfEmpty()
                                select new InspectedChecks2
                                {
                                    Id = ic.Id,
                                    CheckDate = ic.CheckDate,
                                    Category = ic.CategoryId.ToString(),
                                    Check = tChecks.TextValue ?? tChecksFallBack.TextValue,
                                    CheckingUser = ic.CheckingUser,
                                    Result = ic.Result,
                                    Passed = ic.Passed,
                                    InspectionId = ic.InspectionId,
                                    ToleranceValue = ic.ToleranceValue,
                                    Images = null,
                                    Unit = ic.Unit
                                }).ToList();

        return Content(JsonConvert.SerializeObject(inspectionChecks));
    }

if the InspectionDetails method is called the following exception is thrown:

System.Data.SqlClient.SqlException: "The multi-part identifier "x0.title" could not be bound. The multi-part identifier "x0.title" could not be bound."

Further infromation: "cp" contains the disired values, so everything works fine till there.

What we tried so far:

  • Run the same query directly on the SQL database --> Success
  • Run two querys in the InspectionDetails method (first query: set a var to the value of "cp", second query: like above, except using with the new var insted of "cp") --> NO Success

Anyone who had a similar issue or knows how to fix it?

Thanks in Advance

maxhoefler
  • 136
  • 2
  • 10
  • What EF Core version are you using? There were (and still are) many bugs, especially with `left outer join` translation. Some are fixed in later versions, that's why I'm asking. – Ivan Stoev Jul 28 '17 at 10:36
  • Have you considered using `join` rather than multiple `from` clauses @maxhoefler ? https://stackoverflow.com/a/39922904/34092 – mjwills Jul 28 '17 at 10:39
  • 2
    Also what do you mean by *Run the same query directly on the SQL database* - what query, all we see here is LINQ query. – Ivan Stoev Jul 28 '17 at 10:40
  • @Ivan Stoev we are using EF Core 1.0.0. Might that cause the problem? – maxhoefler Jul 28 '17 at 11:03
  • 2
    As I said, EF Core has issues with left joins. Some of them have been fixed, the only way to confirm that is to try your query on the latest official 1.1.2. But we can't do that for you because we don't have your model. – Ivan Stoev Jul 28 '17 at 11:24
  • Thaks @Ivan Stoev for your answer. I updated the EF Core nuget to 1.1.2 but sadly no success – maxhoefler Jul 31 '17 at 07:23
  • Too bad. So it's a bug and not fixed yet :( Have to seek for a workaround then, which usually is playing with alternative equivalent LINQ queries. Try replacing `from tChecks in _context.Translations.Where(x => x.TranslationKey == cp.Title && x.Lang.ToLower() == lang.ToLower()).DefaultIfEmpty()` with `let tChecks = _context.Translations.FirstOrDefault(x => x.TranslationKey == cp.Title && x.Lang.ToLower() == lang.ToLower())` (and similar for `tChecksFallBack`) and see if that helps. – Ivan Stoev Jul 31 '17 at 07:35
  • 1
    Also no success, got this one -> System.InvalidOperationException: "No value provided for required parameter '_outer_Title'." Thanks Ivan for your help, we solved it with multiple queries. There should be a better solution, but it works for the moment – maxhoefler Aug 01 '17 at 08:34

0 Answers0