Query SQL table using Entity Framework

Question

I am new to Entity Framework, and I am trying to query one of my tables, and only return the data from one column. I have used this code:

using (var ctx = new TestEntities())
{
    var Pokemon = ctx.C__PokemonName
                     .SqlQuery("select pokemon from __pokemonname")
                     .ToList();

    foreach (var pokemonname in Pokemon)
        MessageBox.Show(Convert.ToString(pokemonname));
}

This code throws an error:

System.Data.Entity.Core.EntityCommandExecutionException
HResult=0x8013193C
Message=The data reader is incompatible with the specified 'TestModel.C__PokemonName'. A member of the type, 'ID', does not have a corresponding column in the data reader with the same name.

Source=EntityFramework

StackTrace:
at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary2 renameList) at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary2 renameList)
at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary2 renameList) at System.Data.Entity.Core.Objects.ObjectContext.InternalTranslate[TElement](DbDataReader reader, String entitySetName, MergeOption mergeOption, Boolean streaming, EntitySet& entitySet, TypeUsage& edmType) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass691.b__68()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass691.b__67()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) at System.Data.Entity.Internal.Linq.InternalSet1.<>c__DisplayClass11.b__10()
at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at PokeForm.Form1..ctor() in F:\VS Projects\PokeForm\PokeForm\Form1.cs:line 28
at PokeForm.Program.Main() in F:\VS Projects\PokeForm\PokeForm\Program.cs:line 19

Why are you using SqlQuery instead of using EF how it was designed to work? — DavidG
– DavidG, Commented Apr 13, 2019 at 19:11
If you want, you can add your __pokemonname model class to question, so I can cross-check if my answer is correct and add some explanation.. — Risto M
– Risto M, Commented Apr 13, 2019 at 19:34

Risto M · Accepted Answer · 2019-04-13 19:26:51Z

1

Easiest way to select * (select all columns) is following:

using (var ctx = new TestEntities())
{
    var pokemonList = ctx.C__PokemonName.ToList();

    foreach (var pokemon in pokemonList)
        MessageBox.Show(Convert.ToString(pokemon.pokemonname));
}

If you want to query only for single column, you should use i.e Dapper-library with custom sql (select pokemonname from __pokemonname) or you can select one column with Linq .Select-operation and projected anonymous type.

using (var ctx = new TestEntities())
{
    var pokemonNameList = ctx.C__PokemonName.Select(r => new { pokemonname = r.pokemonname }).ToList();

    foreach (var name in pokemonNameList)
        MessageBox.Show(Convert.ToString(name.pokemonname));
}

edited Apr 13, 2019 at 19:26

answered Apr 13, 2019 at 19:03

Risto M

3,0291 gold badge17 silver badges29 bronze badges

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

2 Comments

Doctor Ford Over a year ago

Is there a way to select ascending?

Risto M Over a year ago

Yes you can select ascending also. Add: .OrderBy(r=>r.pokemonname) before .ToList()

Collectives™ on Stack Overflow

Query SQL table using Entity Framework

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related