none
FirstOrDefault in EntitySQL RRS feed

  • Question

  • In a LINQ query if I need just the first item returned by a query, I attach .FirstOrDefault() (or one of the other three methods based on what exactly do I want), what's the counterpart in EntitySQL? 

    SELECT value TOP (1) ...

    doesn't work because it still returns a collection of objects,

     SELECT TOP(1) value ...

    is invalid syntax and searching the docs failed.

     

    And yes, I do need it in EntitySQL ... I need it for a model defined function and those are defined using EntitySQL, not LINQ.

     

    Thanks, Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Monday, September 19, 2011 2:37 PM

Answers

  • Hi.

    In ESql you can use ANYELEMENT operator that returns single element from a collection of elements. In case if it is an empty collection the return value is NULL.

    Here is an example how to use it in function definition:

    <Function Name="GetSingleMyTable" ReturnType="TestModel.MyTable">
          <DefiningExpression>
                ANYELEMENT(select VALUE TOP(1) c FROM TestEntities.MyTables as c order by c.Id)
           </DefiningExpression>
     </Function>
    
    

    where TestModel - is a model's namespace, and TestEntities - entity container.

    Example of using the model defined function in a code:

    var sql = "TestModel.GetSingleMyTable()";
    MyTable t = context.CreateQuery<MyTable>(sql).Execute(MergeOption.NoTracking).Single();
    if (t != null)
      Console.WriteLine(t.MyColumn);
    
    

     

    Regards

    • Marked as answer by JendaPerl Monday, September 19, 2011 9:20 PM
    Monday, September 19, 2011 7:28 PM

All replies

  • On 9/19/2011 10:37 AM, JendaPerl wrote:
    > In a LINQ query if I need just the first item returned by a query, I
    > attach .FirstOrDefault() (or one of the other three methods based on
    > what exactly do I want), what's the counterpart in EntitySQL?
    >
    > SELECT value TOP (1) ...
    >
    > doesn't work because it still returns a collection of objects,
    >
    > SELECT TOP(1) value ...
    >
    > is invalid syntax and searching the docs failed.
    >
    > And yes, I do need it in EntitySQL ... I need it for a model defined
    > function and those are defined using EntitySQL, not LINQ.
    >
     If you are using EntitySQL, then you have to use a datareader and stop
    after the first read to get the first object. If using EntitySQL based
    on you using it in the manner of Linq query, then you use a foreach loop
    on the returned collection and stop after the first iteration through
    the collection.
     
    Monday, September 19, 2011 4:12 PM
  • There´s no place to put the data reader!

    I need an EntitySQL expression that returns a single entity.

    http://blogs.msdn.com/b/efdesign/archive/2009/01/07/model-defined-functions.aspx


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Monday, September 19, 2011 4:15 PM
  • On 9/19/2011 12:15 PM, JendaPerl wrote:
    > There´s no place to put the data reader!
     
     
    You can put code in the datareader loop to instantiate a new entity,
    populate the entity from the datareader and return the single entity
    after one iteration through the datareader.
     
    If you have ever used a ADO.NET datareader with SQL command objects and
    T-SQL, then there is no difference. You can even user the string field
    names in the Get instead of using ordinals.
     
    Public JendaPerl GetFirstOne()
    {
        var jp = new JendaPerl()
         do open and EntitySQL stuff
         while reader
        {
          populate jp from reader
          stop after one read
        }
        return jp;
    }
     
    Of course JendaPerl would be an entity on the model.
     
    Monday, September 19, 2011 4:55 PM
  • Hi.

    In ESql you can use ANYELEMENT operator that returns single element from a collection of elements. In case if it is an empty collection the return value is NULL.

    Here is an example how to use it in function definition:

    <Function Name="GetSingleMyTable" ReturnType="TestModel.MyTable">
          <DefiningExpression>
                ANYELEMENT(select VALUE TOP(1) c FROM TestEntities.MyTables as c order by c.Id)
           </DefiningExpression>
     </Function>
    
    

    where TestModel - is a model's namespace, and TestEntities - entity container.

    Example of using the model defined function in a code:

    var sql = "TestModel.GetSingleMyTable()";
    MyTable t = context.CreateQuery<MyTable>(sql).Execute(MergeOption.NoTracking).Single();
    if (t != null)
      Console.WriteLine(t.MyColumn);
    
    

     

    Regards

    • Marked as answer by JendaPerl Monday, September 19, 2011 9:20 PM
    Monday, September 19, 2011 7:28 PM
  • Thanks Rustam! This is exactly what I was looking for!

     

    Thanks god some people actually do read the posts they reply to.


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Monday, September 19, 2011 9:23 PM
  • On 9/19/2011 5:23 PM, JendaPerl wrote:
    > Thanks Rustam! This is exactly what I was looking for!
    >
    > Thanks god some people actually do read the posts they reply to.
    >
    There are more than one way to skin the same cat. You can't honestly
    think that what I told you wouldn't work. You remember what I told you,
    because some shops won't allow the use of an ORM period, and its back to
    T-SQL or P-SQL and a datareader and the ORM is not going to save you.
     
    Monday, September 19, 2011 11:00 PM
  • There may very well be several ways to skin the same cat ... it doesn't make sense to tell me how to peel a potato though if I ask specifically how to skin a cat.
    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, September 20, 2011 7:17 AM
  • On 9/20/2011 3:17 AM, JendaPerl wrote:
    > There may very well be several ways to skin the same cat ... it doesn't
    > make sense to tell me how to peel a potato though if I ask specifically
    > how to skin a cat.
     
    Whatever...... :)
     
    Tuesday, September 20, 2011 11:48 AM