none
How to do this in LINQ RRS feed

  • Question

  • Is there some way to  convert this SQL query to LINQ? The table being queried has a column named guid that contains 'uniqueidentifier' values that correspond to Guids.

    select distinct guid from chan where guid like '21EC2020%'



    Richard Lewis Haggard

    Tuesday, February 7, 2012 2:53 AM

Answers

  • I ended up doing this in stages. First I collected the smallest set of results that I could (there were other constraints on the select that I did not include in the forum question since they had nothing to do with the root question) and then I added code to convert the result set guid's to strings so that I could do the programmatic equivelent of a LIKE. Those result members that did not satisfy the requirements were removed from the result set.

    Richard Lewis Haggard

    Monday, February 20, 2012 1:29 AM

All replies

  • Hi Richard,

    Welcome!

    We often use Like for strings, as far as I know, it's hard to convert the SQL query to LINQ for Guid Type. You can refer this link for Like operator in LINQ: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/16/linq-to-sql-like-operator.aspx

    I suggest you to run "T-SQL" directlly in EF or LINQ to SQL or run SP for the query:

    EF: http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorequery.aspx

    Linq to sql: http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executequery.aspx

    Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, February 7, 2012 7:44 AM
    Moderator
  • Thanks, but due to outside constraints this must be done using entity framework and no tsql, thus none of the suggested approaches are viable. The customer is insisting that linq be used and no sql at all. Likewide, no public domain dynamic linq classes that would by licencing agreements require making the source available to the public which precludes the use of DLINQ.

    I'm guessing that this means I'm going to have to learn how to extend linq to provide my own dynamic linq functionality.


    Richard Lewis Haggard

    Tuesday, February 7, 2012 5:22 PM
  • Hi Richard;

    In order to do this in Entity Framework you will need to use eSQL as the query language because Linq to SQL does not support what you need to do. See code below for Enity Framework eSQL code.

    // Holds the collection GUID's returned from eSQL query
    List<Guid> guids = new List<Guid>();
    
    // Create a connection, The parameter name= in the below string is needed the EntityContainer
    // needs to be changed to match EntityContainer name found in the model
    using( EntityConnection conn = new EntityConnection( "name=EntityContainer" ) )
    {
        conn.Open( );
    
        // Create an EntityCommand.
        using( EntityCommand cmd = conn.CreateCommand( ) )
        {
            // Change EntityContainer in the below string to match EntityContainer name found in the model
            string esqlQuery = "SELECT VALUE DISTINCT c.guid FROM EntityContainer.chan " +
                               "AS c WHERE CAST(c.guid AS Edm.String) LIKE @guidStart ";
                               
            // Value to use in the LIKE function in SQL server
            string startsWith = "21EC2020%";
            // Create a parameter to use in esqlQuery string
            var param = cmd.CreateParameter( );
            param.ParameterName = "guidStart";
            param.Value = "3%";
            cmd.Parameters.Add( param );
            cmd.CommandText = esqlQuery;
            
            // Execute the command.
            using( EntityDataReader rdr = cmd.ExecuteReader( CommandBehavior.SequentialAccess ) )
            {
                // Start reading results.
                while( rdr.Read( ) )
                {
                    IExtendedDataRecord record = rdr as IExtendedDataRecord;
                    // The record contains exactly one field.
                    int fieldIndex = 0;
                    guids.Add( record.GetGuid( fieldIndex ) );
                }
            }
        }
        conn.Close( );
        // The List guids now contain a list of Guid's
    }


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 7, 2012 6:36 PM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?
    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 15, 2012 8:20 AM
    Moderator
  • Richard,

    Have you tried this in linq:

        using (MyDataContext context = new MyDataContext(connectionString))
        {
           var guidList = from c in context.chan
                          where c.guid.ToString().Contains('21EC2020' )
                          select c;
           return guidList;
        }

    Wednesday, February 15, 2012 4:18 PM
  • Richard,

    Have you tried this in linq:

        using (MyDataContext context = new MyDataContext(connectionString))
        {
           var guidList = from c in context.chan
                          where c.guid.ToString().Contains('21EC2020' )
                          select c;
           return guidList;
        }

    And have YOU tried it?

    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Wednesday, February 15, 2012 5:39 PM
  • I ended up doing this in stages. First I collected the smallest set of results that I could (there were other constraints on the select that I did not include in the forum question since they had nothing to do with the root question) and then I added code to convert the result set guid's to strings so that I could do the programmatic equivelent of a LIKE. Those result members that did not satisfy the requirements were removed from the result set.

    Richard Lewis Haggard

    Monday, February 20, 2012 1:29 AM