Find a record in dbSet using Find without a primary key

Question

Find a record in dbSet using Find without a primary key

asp.net-mvc entity-framework linq

Question

I have a users table:

Users:
 +ID
 +Username
 +...

I want to use myDBContext.Users.Find(Username) to fin a users. in my current context I can not use his ID.

do i have to use a full LINQ query ? e.g.

var user = from users in myDBContext.Users.Find(Username) 
           where users.Username == username
           select users

I have also tried to define the username as a primary key in my edmx but that resulted in the following error:

Properties referred by the Principal Role User must be exactly identical to the key of the EntityType CamelotShiftManagementModel.User referred to by the Principal Role in the relationship constraint for Relationship CamelotShiftManagementModel.AssociationUserFK1. Make sure all the key properties are specified in the Principal Role. C:\Code\CamelotShiftManagement\CamelotShiftManagement\Models\CamelotDB.edmx 278 11 CamelotShiftManagement

Mortalus

Popular Answer

I've found it:

User myUser = myDBContext.Users.Single(user => user.Username == i_Username);

Mortalus

View more on Stack Overflow

Jignesh Thakker · Accepted Answer · 5/5/2016 3:43:45 AM

Try with,

User myUser = myDBContext.Users.SingleOrDefault(user => user.Username == username);

Use SingleOrDefault insted of Single. If user doesn't exist then Single will throw an error. While SingleOrDefault will return null if user not found otherwise User object will be return.

Selection Between SingleOrDefault and FirstOrDefault

You can get the user object by using SingleOrDefault and FirstOrDefault but while selecting which method to use consider below points.

Both return only one value from collection/database if exist otherwise default value.
But if you have more than one user with same name and you are expecting to get an exception while performing LINQ query then use SingleOrDefault as it will thrown an exception if there are more than one element available.
And if you don't want exception and/or you don't want to check that your collection/database have duplication of data, just want to get first value from collection/database then use FirstOrDefault for better performance compare to SingleOrDefault

FirstOrDefault

Generally FirstOrDefault or First used when we required single value (first) from the collection or database.
In the case of Fist / FirstOrDefault, only one row is retrieved from the database so it performs slightly better than single / SingleOrDefault. such a small difference is hardly noticeable but when table contain large number of column and row, at this time performance is noticeable.

Some other remarks

If username is primary key then I think there will be no much/no difference between SingleOrDefault and FirstOrDefault performance as primary key has index and search on index column will always be faster than normal column.
Single or SingleOrDefault will generate a regular TSQL like "SELECT ...".
The First or FirstOrDefault method will generate the TSQL statment like "SELECT TOP 1..."

Find a record in dbSet using Find without a primary key

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related