-2

I have this Linq expression (FirstOrDefault) and it's very slow. Options is an DbSet

 DbOption dbo = ctx.Options.FirstOrDefault(o =>
                        o.SynN == oLoaded.SynN &&
                        o.OwnerId == oLoaded.OwnerId &&
                        o.Context == oLoaded.Context);

There is a way faster to find my options? I see with "Expression", but how can I convert this Linq code in Expression? I have never used it.

Thanks for the support

scorona85
  • 97
  • 6
  • 1
    The expression looks suspicious. Why are you comparing the same property? `o.SynN == o.SynN` and `o.Context == o.Context`? – Sebastian Siemens Apr 03 '23 at 06:56
  • 1
    You can reduce the expression to `.FirstOrDefault(o => o.OwnerId == o.Id)` - the rest is obsolete. – Sebastian Siemens Apr 03 '23 at 06:57
  • Do you have a composite index on SynN, OwnerId, and Context? – ProgrammingLlama Apr 03 '23 at 07:00
  • No, I have change my code. I have simplify the code for the post in a wrong mode. Sorry. Now It's ok – scorona85 Apr 03 '23 at 07:00
  • Based on the code, really all that can be said is: no, there's no faster way. FYI, `(o => ...)` *is* an expression. – Gert Arnold Apr 03 '23 at 07:04
  • 1
    Post your *table* schema and indexes. EF translates LINQ queries to SQL. What you posted will be translated to `SELECT TOP 1 * FROM Options where SynN=@param1 AND OwnerID=@param2 and Context=@param3)`. Query performance depends on the table indexes and statistics. Without the proper indexes the database will have to scan the entire table to find matches. – Panagiotis Kanavos Apr 03 '23 at 07:04
  • You should only load the data you need too. If you only need a specific option value, use `Select(o=>o.Value)` to generate `SELECT TOP 1 Value` instead of `SELECT TOP 1 *`. This may not be that significant when loading a single small row, but can cause significant delays if you load a wide table with lots of `varchar(max)` or `varbinary(max)` fields. – Panagiotis Kanavos Apr 03 '23 at 07:09

1 Answers1

1

In case of too many records, you can create a index to improve the performance of your query against the database

CREATE NONCLUSTERED INDEX [IX_Options_SynN_OwnerId_Context] ON [dbo].[Options]
(
    [SynN] ASC,
    [OwnerId] ASC,
    [Context] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
fubo
  • 44,811
  • 17
  • 103
  • 137