Non clustered indexing does not reflect in the query performance

See more:

C#

SQL

LINQ

performance

indexes

I have a table which logs the user logins and the application from which user has logged.

| S.No | UserID | Application | DateTime |
-------------------------------------------

This table has more than 1 million records.

I am querying the list of records using LINQ based on list of userid and application below.

List<string> relyingParties = new List<string>(){"AAA","BBB","CCC","DDD"};

//Filtering the users by UserID and Application
var userLogActionList = dbContext.LogUserAction.Where(x => userIDList.Contains((x.UserID.Value)) && relyingParties.Contains(x.ApplicationLoggedIn)).ToList());

The avg time it takes to fetch from DB takes more than 15 seconds for a input of 100 userids in userIDList which is very bad i feel.

I want to have a better performance for querying and fetching the data. I came across Indexing while searching across the web.

I am very new to indexing concept. Since I have a PK in the table already it has a clustered index so i was not able to create another clustered index in the table.
I have implemented a non clustered index to column UserID.

IX_LogUserAction_UserID

If i run a SQL query in SQL server,

SELECT  *
  FROM [XYZDB].[dbo].[LogUserAction] 
  WITH (INDEX([IX_LogUserAction_UserID]))
  where UserID='0cef394e-06ac-49cf-8c62-2a37299913e1'

I can see the execution path plan for the seek and Table Scan(when no indexing is there).

As far as my knowledge, if indexing is applied to a column it will increase the query performance.

But if I run the same Linq code above, where i try fetch records the performance is still the same(Around 15 seconds).

Can someone help me in detailing, what i am doing wrong or where am i going wrong?

Your response is highly appreciated.

What I have tried:

List<string> relyingParties = new List<string>(){"AAA","BBB","CCC","DDD"};

//Filtering the users by UserID and Application
var userLogActionList = dbContext.LogUserAction.Where(x => userIDList.Contains((x.UserID.Value)) && relyingParties.Contains(x.ApplicationLoggedIn)).ToList());

SELECT  *
  FROM [XYZDB].[dbo].[LogUserAction] 
  WITH (INDEX([IX_LogUserAction_UserID]))
  where UserID='0cef394e-06ac-49cf-8c62-2a37299913e1'