LINQ need to parse JSON out of a column

Question

In a table, Leads, there is a column Data that contains a JSON string. In a LINQ statement, I need to extract a field from that JSON:

var results = from l in leads
    select new MyLeadObject
    {
        LeadID = l.LeadID,
        ...
        RequestType = (string)l.Data["RequestTypeID"]
    };

Here's a shortened version of the JSON:

{  
   "RequestTypeID":1
}

RequestTypeID is a string.

I've been reading other threads and trying to cobble this together. Not having much luck.

EDIT:

With help from Nkosi, I got this far:

RequestType = (string)JSONNetSerialization.DeserializeJsonNet<LeadData>(l.Data).RequestTypeID

The only problem is that LeadData.RequestTypeID is an enum, so it won't convert the enum to a string. I'm not sure how to get the value of the enum instead of the entire enum itself. Outside of LINQ I could do this: RequestTypeID.GetDisplayName(); but .GetDisplayName() is not recognized by LINQ.

You can use JSON.Net to parse the Data field to get the property. But the query would need to be realized first as linq to sql will kick up a fuss — Nkosi
– Nkosi, Commented Nov 26, 2016 at 14:46
is leads a direct call to the table or is it already in memory — Nkosi
– Nkosi, Commented Nov 26, 2016 at 14:49
It is in memory. Previously, it was populated with a call to the DB to load all records from the Lead table — Casey Crookston
– Casey Crookston, Commented Nov 26, 2016 at 14:52

Nkosi · Accepted Answer · 2016-11-26 15:11:54Z

1

You can use Json.Net to parse the JSON in Data field to get the property.

var results = leads.Select(l =>
    new MyLeadObject {
        LeadID = l.LeadID,
        //...
        RequestType = (string)JsonConvert.DeserializeObject(l.Data)["RequestTypeID"]
    });

edited Nov 26, 2016 at 15:11

answered Nov 26, 2016 at 14:55

Nkosi

249k38 gold badges472 silver badges506 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Casey Crookston Over a year ago

I'm getting: An expression tree may not contain a dynamic operation

Casey Crookston Over a year ago

I have an actual object I could use instead of dynamic. But it's complicated by the fact that RequestTypeID is an enum

Casey Crookston Over a year ago

getting close! DeserializeObject still wants a type. I tried this, but this isn't it either: DeserializeJsonNet<string>(l.Data)["RequestTypeID"]

Casey Crookston Over a year ago

C#, NET 4.5, MVC, VS2015 -- hope one of those answers your question. Sure do appreciate your help!

Nkosi Over a year ago

And you have latest version of Json.Net referenced?

|

Collectives™ on Stack Overflow

LINQ need to parse JSON out of a column

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related