C# MVC ASP.NET Most Efficient way to get data from database with id

Question

I'm new to MVC and databases, but I'm fairly certain the way that I'm getting data from the database is incredibly inefficient.

public class PokemonViewController : Controller
{
    private PokemonDayCareDBEntities1 db = new PokemonDayCareDBEntities1();

    [Authorize]
    public ActionResult Pokemon(int id)
    {
        var pkmn = db.PlayerPkmns.ToList();
        PlayerPkmn thispkmn = null;

        //get pokemon from database where id = id
        foreach (var item in pkmn)
        {
            if (item.Id == id)
            {
                thispkmn = item;
            }
        }

....

I have the unique ID of the Pokemon that resides in the PlayerPkmns table, but I'm looping through the entire table in order to find the matching ID.

Depending on how large the table, the time this takes to execute would increase.

I'm positive there's a better way - would anyone know if there is one (also, the syntax on how to use it)

Thanks!

You need to get familiar with Linq-to-Entities . btw the code you would need is db.PlayerPkmns.Where(x=>x.Id == id).FirstOrDefault(); — Rajshekar Reddy
– Rajshekar Reddy, Commented May 22, 2017 at 12:30
@Alex isn't that the same as using where I mean behind the scenes.. — Rajshekar Reddy
– Rajshekar Reddy, Commented May 22, 2017 at 12:34
@Alex I see it as a better readability.. For newbies at least.. — Rajshekar Reddy
– Rajshekar Reddy, Commented May 22, 2017 at 12:36

Alex · Accepted Answer · 2017-05-22 12:29:50Z

7

SingleOrDefault is your friend here.

Change this:

var pkmn = db.PlayerPkmns.ToList();

To

var pkmn = db.PlayerPkmns.SingleOrDefault(x => x.Id == id);

This will translate (roughly) into the following SQL

SELECT TOP 1 * FROM PlayerPkmns WHERE ID = @id

answered May 22, 2017 at 12:29

Alex

38.6k55 gold badges217 silver badges340 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

impo Over a year ago

thank you! I know it was a basic question, but I wasn't sure about the syntax (i think it's called a lambda expression)? Thank you!

Joe Ricklefs · Accepted Answer · 2017-05-22 12:37:27Z

3

Most effecient way to retrieve an entity is to use the .Find() Where primaryKey is the Id you are passing in.

var PlayerPkmn= db.PlayerPkmns.Find(primaryKey)

answered May 22, 2017 at 12:37

Joe Ricklefs

6606 silver badges27 bronze badges

Comments

Ehsan Sajjad · Accepted Answer · 2017-05-22 12:29:45Z

1

As you are using Linq to Entities, you should be able to use the Where() in combination with FirstOrDefault() :

var pkmn = db.PlayerPkmns.Where(x=>x.Id  == id).FirstOrDefault();

The way you are doing right now will cause all the records to be brought in memory which is not what you want i believe, and it will be much performant than the way you are doing right now, as with the way you are doing, it will load all records in to the memory.

answered May 22, 2017 at 12:29

Ehsan Sajjad

62.6k16 gold badges113 silver badges171 bronze badges

7 Comments

Carles Company Over a year ago

There's no need to use Where: you can also pass a predicate ti FirstOrDefault.

Ehsan Sajjad Over a year ago

@CarlesCompany there are multiple ways for doing that, in both case sql will be generated similar i guess and from db side only 1 row will be returned

Rajshekar Reddy Over a year ago

@EhsanSajjad is right... unless there is no improvements in any metrics I think all the ways are acceptable..

Alex Over a year ago

If you use resharper, this will show up as a warning. I like a green light on all my files :)

Ehsan Sajjad Over a year ago

unfortunately i don't have resharper :/

|

polkduran · Accepted Answer · 2017-05-22 12:32:32Z

-1

I suppose that PokemonDayCareDBEntities1 is an Entity Framework Data Context. In that case you can use db.PlayerPkmns.Find(id) or db.PlayerPkmns.SingleOrDefault(x => x.Id == id) if you want to handle the case where the entity does not exist in the DB, db.PlayerPkmns.Single(x => x.Id == id) if you are sure that there is strictly one element in the DB (will throw if the entity does not exist).

answered May 22, 2017 at 12:32

polkduran

2,56024 silver badges36 bronze badges

Collectives™ on Stack Overflow

C# MVC ASP.NET Most Efficient way to get data from database with id

4 Answers 4

1 Comment

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related