Can my C# SQL CLR Procedure use LINQ?

Question

I have a C# SQL CLR proc that uses DataTables. In a few places, I'm hoping to query the database using

LINQ statements?

Is that possible?

.Net 4.7.2

VS 2019

SQL Server 2012

Answer 1

Hi @Coreysan , Welcome to Microsoft Q&A,

If you can't use Alt+enter to quickly add the Linq library. You can add it manually via:

In Framework 4.7.2

1.Right click to open reference.

2.Add linq library

using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp13
{
     class Program
     {
         static void Main(string[] args)
         {

             //Create a List containing data (simulated data source)
             var memberRows = new List<MemberRow>
         {
             new MemberRow { STARTDATE = new DateTime(2023, 1, 1) },
             new MemberRow { STARTDATE = new DateTime(2022, 5, 15) },
             new MemberRow { STARTDATE = new DateTime(2023, 8, 10) }
         };

             // Use LINQ query syntax to find the minimum and maximum values of the STARTDATE column
             DateTime minStartDate = memberRows.Min(row => row.STARTDATE);
             DateTime maxStartDate = memberRows.Max(row => row.STARTDATE);

             Console.WriteLine("Minimum STARTDATE: " + minStartDate);
             Console.WriteLine("Maximum STARTDATE: " + maxStartDate);

             // Sort data using LINQ method chain
             var sortedRows = memberRows.OrderBy(row => row.STARTDATE);

             Console.WriteLine("Sorted Data:");
             foreach (var row in sortedRows)
             {
                 Console.WriteLine("STARTDATE: " + row.STARTDATE);
             }

             Console.ReadLine();
         }

       
     }

     //Define a simple data class to store STARTDATE
     class MemberRow
     {
         public DateTime STARTDATE { get; set; }
     }

}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Here's a simplified source code you might use LINQ in a SQL CLR procedure:

using System.Data;
using System.Data.SqlClient;
using System.Data.Linq;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void YourCLRProcedure()
    {
        // Set up a connection to the database
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();

            // Create a LINQ DataContext
            DataContext dataContext = new DataContext(connection);

            // Define your LINQ query
            var query = from c in dataContext.GetTable<YourTable>()
                        where c.SomeColumn == "SomeValue"
                        select c;

            // Execute the LINQ query and process the results
            foreach (var result in query)
            {
                // Process the result here
                SqlContext.Pipe.Send(result.ToString());
            }
        }
    }
}