Handle non traditional storage querying methodology

[meikeric]

Currently we have a message queue that is persisted to SQL. It's a simple table structure: Message(MessageId, Timestamp, MessageContent) essentially.
When we process a message we have a meta table with distinct fields for later querying MessageMeta (MessageId, FirstName, LastName) that we query to pull the list of messages that match and then we can pull the entire message from the Message table.

Is there a way to abstract away this concept for the where clause and then deserialize MessageContent through a deserializer we already have to a CS object so we can run Linq queries on it?

Ideally some form of attributes would be awesome, eg.
[MetaFilter(table: "MessageMeta", key: "MessageId", filterFields: new List() {"FirstName", "LastName"}]

Any guidance would be extremely appreciated.

[rowanmiller]

Hey,

We discussed this as a team but weren't able to work out exactly what you are after. Can you share an example of the code you would want to write for your entity classes, the LINQ query you want to be able to write, the SQL that would be generated, and what the results would look like.

~Rowan

[meikeric]

Hi Rowan,
Thank you and your team for reviewing. I hope the below code makes sense. Please let me know if you have any questions.

The code below is a very simple example, but our actual use case (HL7 v2 messages: https://en.wikipedia.org/wiki/Health_Level_7#HL7_version_2 ) is a lot more complex of a serialized message object which is why I've spent the time trying to develop this example. We have a custom serializer and deserializer for this, but I've substituted JSON for ease of understandibility.

I believe if your team implemented something similar to this it would allow for a lot of instances when you don't want an entire relational database around your data, but still need ease to query it, a psuedo NOSQL layer on top of SQL server if you will.

    public class Program
    {
        public static void Main()
        {
            // Example of how I'd like to write my code.
            var coolMsgs = DbContext.Message.Where(msg => msg.FirstName == "Michael" && msg.Gender == "Male").ToList();

            //Instead of...
            // Handle MetaQueryFields against Meta table first.
            var messagesMeta = DbContext.MessageMeta
                .Where(mm => mm.FirstName == "Michael")
                .Select(mm => new { MessageId = mm.MessageId }).ToList();

            // Then we Deserialize to local objects
            var messagesFromDb = DbContext.MessageStore
                .Where(msg => messagesMeta.Contains(msg.MessageId))
                .Select(
                    m => JsonConvert.DeserializeObject<Message>(m.MessageContent)
                ).ToList();

            //Finally we handle querying on the deserialized object.
            var lameMsgs = messagesFromDb.Where(msg => msg.Gender == "Male").ToList();

            var msgs = coolMsgs;

            foreach (var msg in msgs)
            {
                Console.WriteLine("MessageId: {0}, FirstName: {1}, LastName: {2}, Gender: {3}", msg.MessageId, msg.FirstName, msg.LastName, msg.Gender);
            }
        }
    }

    public class DbContext
    {
        public DbSet<MessageStore> MessageStore;
        public DbSet<MessageMeta> MessageMeta;

        [Serialized]
        public List<Message> Messages;
    }

    [Table("Messages")]
    [DeserializeOutTo(typeof(Message), "MessageContent")]
    public class MessageStore
    {
        [Key]
        public string MessageId { get; set; }

        public string MessageContent { get; set; }
    }

    [Table("MessageSearch")]
    public class MessageMeta
    {
        [Key]
        public string MessageId { get; set; }

        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    [SerializeInto(typeof(MessageStore))]
    [MetaQuery(typeof(MessageMeta))]
    [JsonObject]
    public class Message
    {
        [Key]
        public string MessageId { get; set; }

        [MetaQueryField("FirstName")]
        public string FirstName { get; set; }

        [MetaQueryField("LastName")]
        public string LastName { get; set; }

        public string Gender { get; set; }
    }

    public class DeserializeOutToAttribute : Attribute
    {
        public string ClassName;
        public string StoredInField;

        public DeserializeOutToAttribute(string className, string storedInField)
        {
            this.ClassName = className;
            this.StoredInField = storedInField;
        }
    }

    public class SerializeIntoAttribute : Attribute
    {
        public string ClassName;

        public SerializeIntoAttribute(string className)
        {
            this.ClassName = className;
        }
    }

    public class MetaQueryAttribute : Attribute
    {
        public string ClassName;

        public MetaQueryAttribute(string className)
        {
            this.ClassName = className;
        }
    }

    public class MetaQueryFieldAttribute : Attribute
    {
        public string FieldName;

        public MetaQueryFieldAttribute(string fieldName)
        {
            this.FieldName = fieldName;
        }
    }

[rowanmiller]

Hey,

Thanks for the details, that makes sense now. This boils down to a couple of features that are needed to achieve this.

• Ability to map multiple properties to a single column (by serializing/deserializing to JSON, xml, etc.)
• Ability to query based on values stored in a JSON column

[rowanmiller]

This had a lot of overlap with #2141, so I generalized that issue a bit to cover this whole area.