none
How to do return array or list<string> Entity Framework ? RRS feed

  • Question

  • Hello guys, I have a function with store procedure entity framework, i want to return as JSON string .

    public static string GetGroupModFunc(string group_mod_id) { var idParam = new SqlParameter { ParameterName = "GID", Value = group_mod_id }; var obj = db.Database.SqlQuery<String>("EXEC GET_GROUP_PERMIT @GID", idParam).ToList();

    //var obj = db.Database.SqlQuery<string>("EXEC GET_GROUP_PERMIT @GID", idParam).FirstOrDefault(); return JsonConvert.SerializeObject(obj); }

    It's error

    System.Data.Entity.Core.EntityCommandExecutionException: The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.
       at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 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__DisplayClass26`1.<ExecuteStoreQueryReliably>b__25()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass26`1.<ExecuteStoreQueryReliably>b__24()
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 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, ExecutionOptions executionOptions, Object[] parameters)
       at System.Data.Entity.Internal.InternalContext.<>c__DisplayClassf`1.<ExecuteSqlQuery>b__e()
       at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    You can give me some advice.Thank you. Ago, I have seen ref at http://stackoverflow.com/questions/23910390/the-data-reader-has-more-than-one-field-multiple-fields-are-not-valid-for-edm-p , but it's not still work





    Monday, May 18, 2015 6:51 AM

Answers

  • I found solution for this issue : - Get data of store to DataTable , then conver data from DataTable to JSON. It's work fine.
    • Marked as answer by headshot9x9 Wednesday, May 20, 2015 6:54 AM
    Wednesday, May 20, 2015 6:54 AM

All replies

  • I mean it is telling you what in wrong. Somehow you have returned a resultset from the T-SQL that has more than one column of data in the obj that a ADO.NET datareader is trying to read.

    You are going yo have to be in some kind of a foreach loop and address each object in the collection. I would think.

    http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

    Monday, May 18, 2015 8:44 PM
  • Well, I try it so much but it's not work.
    Note : Result a store procedure have  data is dynamic, it is not in entity instances. Column Name is a string and the value is an interger . I think should return object or array.The data is dynamic. the number of columns and values are not fixed (maybe 3 as show or 5 columns , 10 columns, etc.), should not be declared an entity or a class that contains the return value, it does not belong an entity at all.

    //pseucode 
    array[0]=Column_name
    array[0].value= value
    ///or object
    name = column_name
    value = value
    
    Can you give me some code. Thank you so much.

    Tuesday, May 19, 2015 1:33 AM
  • Hello headshot9x9,

    >>Note : Result a store procedure have  data  is dynamic, it  is not in entity instances.  Column Name is a string and  the value is an interger . I think should return object or array.

    Entity Framework would populate data to local object according to the property name, however, in your application, you do not know these returned column names, then it is not possible to let Entity Framework fill data to a List<T> or an array. You might write a common DTO class which contains all possible columns which would return from the store produce.

    Another way is that you could use ado.net approach as below to fill a DataTable and do what DA924 mentions to be in some kind of a foreach loop and address each object in the collection

    string sql = "Pro20150519";//replace this with your store procedure name      
    
                SqlConnection con = new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=DFDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
    
                con.Open();
    
                SqlCommand cmd = new SqlCommand(sql, con);
    
                cmd.CommandType = CommandType.StoredProcedure;
    
                //SqlDataReader reader = cmd.ExecuteReader();
    
    
                SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                DataTable schema = new DataTable();
    
                da.Fill(schema);
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 19, 2015 2:38 AM
    Moderator
  • I found solution for this issue : - Get data of store to DataTable , then conver data from DataTable to JSON. It's work fine.
    • Marked as answer by headshot9x9 Wednesday, May 20, 2015 6:54 AM
    Wednesday, May 20, 2015 6:54 AM