linq query for sql xml column

Question

I would need to write a linq query in c# for the following sql query. I have no issues in implementing where, not in, orderby and descending but the problem is to query a sql xml column

 SELECT [ID]
   ,[QueuedTime]
   ,Parameters.query('data(Root/Root/@type)').value('.', 'varchar(500)') as CommandName //how to implement this line in linq
   ,[Status]
   ,[CurrentRetryCount]
   ,[MaxRetryCount]
   ,[RetryDelaySeconds]
   ,[CompletedTime]
   ,[LastTriedTime]
   ,[LastError]
   ,Parameters
   ,[PrincipalString]
   FROM [AppServer].[dbo].[RequestQueue]
   where interfacename = 'ICommunicationsService'
  and MethodName = 'ProcessCommand'
  and status not in (1,2)
   order by id desc

The following query will meet where, not in and order by descending conditions. I am concerned about how do I implement 'Parameters.query('data(Root/Root/@type)').value('.', 'varchar(500)') as CommandName' in linq

   var unwantedStatus = new[] { 1, 2 };

   var operationTimedOutTasks = context.TblRequestQueues
                                                .Where(t => t.MethodName == "ProcessCommand" && !unwantedStatus.Contains(t.Status))
                                                .OrderByDescending(t => t.ID)                                                
                                                .ToList();

Something like this might work, although I'm not sure how performant it would be — Diado
– Diado, Commented Feb 19, 2018 at 15:14

ChinnaR · Accepted Answer · 2018-02-22 10:08:05Z

The following has resolved my issue.

var query = from queue in context.TblRequestQueues
                                               where queue.MethodName == methodName
                                               && queue.InterfaceName == interfaceName
                                               && !unwantedStatus.Contains(queue.Status)
                                               orderby queue.ID descending
                                               select new
                                               {
                                                   queue.QueuedTime,
                                                   queue.Parameters,
                                                   queue.Status,
                                                   queue.CurrentRetryCount,
                                                   queue.MaxRetryCount,
                                                   queue.RetryDelaySeconds,
                                                   queue.CompletedTime,
                                                   queue.LastTriedTime,
                                                   queue.LastError,
                                                   queue.PrincipalString
                                               };
                var operationTimedOutTasks = query.AsEnumerable()
                                                .Select(t => new TblRequestQueueDto
                                                {
                                                    QueuedTime = t.QueuedTime,
                                                    Parameters = t.Parameters,
                                                    CommandName = XDocument.Parse(t.Parameters).Element("Root").Descendants("Root").FirstOrDefault().Attribute("type").Value,
                                                    Status = t.Status,
                                                    CurrentRetryCount = t.CurrentRetryCount,
                                                    MaxRetryCount = t.MaxRetryCount,
                                                    RetryDelaySeconds = t.RetryDelaySeconds,
                                                    CompletedTime = t.CompletedTime,
                                                    LastTriedTime = t.LastTriedTime,
                                                    LastError = t.LastError,
                                                    PrincipalString = t.PrincipalString
                                                }).ToList();

Sunil · Accepted Answer · 2018-02-20 06:34:15Z

0

Try with XDocument. (using System.Xml.Linq;)

Example:

var operationTimedOutTasks = (from queue in context.TblRequestQueues
                              where queue.MethodName == "ProcessCommand" 
                                  && !unwantedStatus.Contains(t.Status)
                              let xml = XDocument.Parse(queue.Parameters)
                              orderby queue.ID
                              select new 
                              {
                                //Other columns
                                Parameters = xml.Descendants("Root").FirstOrdDefault().Attribute("type").Value
                              }).ToList();

answered Feb 20, 2018 at 6:34

Sunil

3,44210 gold badges27 silver badges32 bronze badges

1 Comment

ChinnaR Over a year ago

The error I am coming up with is: 'System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.Xml.Linq.XDocument Parse(System.String)' method, and this method cannot be translated into a store expression.''

Collectives™ on Stack Overflow

linq query for sql xml column

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related