none
"SELECT *" in a query that returns anonymous type objects RRS feed

  • Question

  • Hi all,

    is there a way to make this code work?


    ObjectContext context = new ObjectContext();

    ObjectQuery<DbDataRecord> query =
    new ObjectQuery<DbDataRecord>("SELECT * FROM <myTable>",
    Context, MergeOption.NoTracking); var results = query.Execute(MergeOption.NoTracking);


    At run time I get this error

    The query syntax is not valid. Near term '*'

     

    Thanks everybody

    Antonio







    Thursday, November 10, 2011 2:36 PM

Answers

All replies

  • Hi,

    See http://msdn.microsoft.com/en-us/library/bb738573.aspx and the "No support for *" paragraph for a replacement. The issue is that the query does not use SQL but "Entity SQL".

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by A DE Thursday, November 10, 2011 3:51 PM
    Thursday, November 10, 2011 3:30 PM
  • Hi Patrice, thanks for the quick answer!

    Any idea about how I could implement this kind of logic?

    Thursday, November 10, 2011 3:53 PM
  • Have you tried something like "SELECT VALUE item FROM MyTable AS item" as explained in the paragraph I mentionned ?

    Else please be more explicit (what are you trying to do and what is the issue you have ?).

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Thursday, November 10, 2011 4:44 PM
  • Basically I am trying to execute queries, which are built at run time, within an entity framework context.

    The queries could be like this

    "SELECT M.column1, M.column2 FROM mytable as M"

    or this

    "SELECT * FROM mytable as M"

    or this

    "SELECT M1.column1, M2.column2 FROM mytable1 as M1, mytable2 as M2 where M1.id = M2.id"

    As you can understand, the logic of this component should be very flexible and able to execute any kind of query.

     

    Thanks again

     

     

     

    Thursday, November 10, 2011 5:03 PM
  • Or if we take things the other way round what do you need from EF ?

    You could inspect the metadata to implement the SELECT * by generating a query that would generate a SELECT with all the "columns". Depending on what you are trying to do Dynamic Data could be interesting (http://weblogs.asp.net/scottgu/archive/2007/12/14/new-asp-net-dynamic-data-support.aspx)

    But then at some point, it defeats the purpose. If you create a model but don't use it, why to even use EF rather than plain old ADO.NET ?

    I used dynamic data for example to create a general UI to let users to define search criteria on the context entities. So knowing still abit more about the context could help. How do you plan to take advantage of this added flexibility ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Thursday, November 10, 2011 7:13 PM
  • Hi again Patrice,

    I will try to describe the background of my questions.

    My UI has a button, labelled "Export". When the user clicks on it, some data is exported to one XML file; the export operation must be tracked and, because of that, some rows of some tables get updated. The data is always retrieved from two tables, let`s say StaticTable1 and StaticTable2. Everything, every CRUD operation, is done through an EF context and within an entity transaction. Moreover (this is the important part and why I need this added flexibility), the user is able to define custom queries to export additional data.

    Let's say that the user defines this query

    "SELECT M.customColumn1, M.customColumn2 FROM mytable as M"

    At the end, the XML file will look more or less like this one

     

    <Export>
       <StaticTable1>
          <column1>Value1</column1>
          <column2>Value2</column2>
          ...
        </StaticTable1>
       <StaticTable2>
          <column1>Value1</column1>
          <column2>Value2</column2>
          ...
        </StaticTable2>
        <!-- Here is the result of the custom defined queries-->
        <CustomQueryResult>
          <column name="customColumn1">Value1</column>
          <column name="customColumn2">Value2</column2>
          ...
        </CustomQueryResult>
    </Export>
    

    I hope that I have been able to give you a clear overview of the situation.

     

     

     

     



    • Edited by A DE Friday, November 11, 2011 8:59 AM
    Friday, November 11, 2011 8:56 AM