0

I'm trying to check if an index exists in a specific table using EF Core. I tried the solution below:

Entity Framework - How to check if table exists?

return context.Database.SqlQuery<int?>($"SELECT 1 as Value FROM sys.indexes WHERE name = '{indexName}' AND object_id = OBJECT_ID('{schema}.{table}') AND is_unique_constraint = 0").SingleOrDefault() != null;

which evaluates into:

SELECT 1 as Value FROM sys.indexes WHERE name = 'UK_value_type' AND object_id = OBJECT_ID('utl.value_type') AND is_unique_constraint = 0

But this query always returns false. If I run the same query directly (in SSMS), the result is there, since the index 100% exists:

2050106344  UK_value_type   3   2   NONCLUSTERED    1   1   0   0   0   0   0   0   0   0   1   1   0   NULL    NULL    0   0
Thom A
  • 88,727
  • 11
  • 45
  • 75
Rok
  • 33
  • 5
  • 1
    Might be, but I can query the `sys.tables` table just fine, with the same user. Also, I run the SSMS query with the exact same user also. – Rok Sep 01 '23 at 08:12
  • 2
    Remove the quotes and pass schema.table as one parameter. – Gert Arnold Sep 01 '23 at 08:29
  • @GertArnold it's already passed as one parameter though. without quotes, it just throws an error, since string is expected – Rok Sep 01 '23 at 08:37
  • 2
    `SqlQuery` turns interpolated strings into parameters, the parameters are evaluated by the db engine which is type-aware. Just try it and monitor the query. Otherwise use `SqlQueryRaw`. – Gert Arnold Sep 01 '23 at 09:04
  • oh wow, you are right, i will post a comment with your solution – Rok Sep 01 '23 at 10:34

1 Answers1

1

As suggested in the comments, interpolated strings are turned into typed parameters, so if you run SQL Profiler, I expect what you'll see actually being run is:

WHERE name = '@p0' AND object_id = OBJECT_ID('@p1.@p2') AND is_unique_constraint = 0

A simple test case for this is:

var stringIn = "test";

var query1 = context.Database.SqlQuery<string>($"SELECT '{stringIn}' AS Value").SingleOrDefault();
Console.WriteLine($"Query 1: {query1}");

var query2 = context.Database.SqlQuery<string>($"SELECT {stringIn} AS Value").SingleOrDefault();
Console.WriteLine($"Query 2: {query2}");

var query3 = context.Database.SqlQuery<string>($"SELECT Value FROM (VALUES ('test')) AS x (Value) WHERE Value = '{stringIn}'").SingleOrDefault();
Console.WriteLine($"Query 2: {query3 ?? "Null"}");

var query4 = context.Database.SqlQuery<string>($"SELECT Value FROM (VALUES ('test')) AS x (Value)  WHERE Value = {stringIn}").SingleOrDefault();
Console.WriteLine($"Query 4: {query4 ?? "Null"}");

OUTPUT

Query 1: @p0   //Literal string for parameter name
Query 2: test  //Actual parameter value
Query 2: Null  // No results because "Test" != "@p0"
Query 4: test  //Result returned because "Test" = "Test"

Finally, getting SingleOrDefault() then checking for null could be simplified a bit by using Any().

So all in all this should work for you:

return context.Database.SqlQuery<int?>($"SELECT 1 as Value FROM sys.indexes WHERE name = {indexName} AND object_id = OBJECT_ID(CONCAT(QUOTENAME({schema}), '.', QUOTENAME({table}))) AND is_unique_constraint = 0").Any();

ADDENDUM

To address the comments, the likely reason you get an error without quotes is because it was creating malformed SQL:

object_id = OBJECT_ID(@p1.@p2)

This is why in the above I've used CONCAT() (along with QUOTENAME()) to build the object name, ending up with something like:

object_id = OBJECT_ID(CONCAT(QUOTENAME(@p1), '.', QUOTENAME(@p2)))
GarethD
  • 68,045
  • 10
  • 83
  • 123