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

1
27
9/22/2012 4:23:38 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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..."
53
5/5/2016 3:43:45 AM

Popular Answer

I've found it:

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

Related Questions

In entity framework, find an object for a given primary key.

var Customer = from c in datacontext.Customer where c.CustomerID == your_key select c; ...That's assuming your customer table has a CustomerID column and that is the primary key....Using DbSet's Find method:...Customer customer= db.Customer.Find(your_key); ...Using a lambda expression:... var customer= dataContext.Cust...
.net c# entity-framework system.data.sqlite
asked by Icarus

How can I remove a record using its primary key in Entity Framework?

You can use dummy object:...var entity = new YourEntity { Key = yourKey }; context.Entities.Attach(entity); context.Entities.DeleteObject(entity); context.SaveChanges();
entity-framework
asked by Ladislav Mrnka

DbSet.Find method ridiculously slow compared to .SingleOrDefault on ID

Find... calls ...DetectChanges... internally, ...SingleOrDefault... (or generally any query) doesn't. ...DetectChanges... is an expensive operation, so that's the reason why ...Find... is slower (but it might become faster if the entity is already loaded into the context because ...Find... would not run a query but just return the loaded entity)...
dbcontext dbset ef-code-first entity-framework sql-server-ce-4
asked by Slauma

MVC finding row in database not using primary key

Item item = db.Items.FirstOrDefault(i => i.typeID == 1); ...Keep in mind, that if you are not using primary key, then there could be more than one item matching your query. You can use ...FirstOrDefault... as in sample above to get first (if any) item which matches query....To get all items with specified type id, use ...Where...:...var items = db....
c# entity-framework linq
asked by Sergey Berezovskiy

Find a specified generic DbSet in a DbContext dynamically when I have an entity

I have following classes and ...DbContext...:...public class Order:BaseEntity { public Number {get; set;} } Product:BaseEntity; { public Name {get; set;} } public class Context : DbContext { .... public DbSet<Order> Orders { set; get; } public DbSet<Product> Products { set; get; } .... } ...I have a list of objects that wa...
c# code-first dbset entity-framework generics
asked by Masoud

Cannot get DbSet.Find to work with Moq (Using the Entity-Framework)

I think you need to also setup the ...IDbSet::Find.......activeLoanMockSet.Setup(m => m.Find(It.IsAny<object[]>())) .Returns<object[]>(ids => activeLoans.FirstOrDefault(d => d.ID == (int)ids[0]));
c# entity-framework moq
asked by Yuliam Chandra

How to find a List<Object> using Find method of entity framework passing Array as parameter?

You are looking for a ...Contains... query...:...var lServicosInformados = db.Servicos.Where(x => lCodigoServicos.Contains(x.PKId)); ...This assumes ...PKId... is the name of your primary id column (you didn't specify the name).
c# entity-framework entity-framework-6 find
asked by BrokenGlass

Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'

I'm using Entity Framework for the first time, but it seems not working as expected....I have this code:...using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; public static class QueryClass { public static void Query() { using (var context = new MyDbEntities()) { DbSet<My...
c# entity-framework linq
asked by Fylax

DBSet<entity> class not found (Entity Framework loading wrong DLL?)

Check your casing. I believe it is: ...DbSet<T>.... So, in this case:...public DbSet<Page> Pages { get; set; }
.net asp.net-mvc c# entity-framework-6 visual-studio
asked by John Laffoon

Is there a way to generically mock the DbSet.Find method with Moq?

I'm currently using an extension method to generically mock DbSets as a list:... public static DbSet<T> AsDbSet<T>(this List<T> sourceList) where T : class { var queryable = sourceList.AsQueryable(); var mockDbSet = new Mock<DbSet<T>>(); mockDbSet.As<IQueryable<T>>().Setup(m => m.Provider).Returns(queryable.Provider);...
c# entity-framework entity-framework-6 generics moq
asked by C. Williamson

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow