How to store mongo ObjectID into SQL Server?

Question

We are transferring some data from MongoDB to SQL Server. To store mongo's objectid in SQL Server, I have a column of type varbinary(12).

Question #1: is this a correct way to store mongo's objected in SQL Server?

Also when I convert objectid using

select CONVERT(varbinary(12),'54aedb94e6c12b1c0e83385f')

I get back 0x353461656462393465366331

Question #2: in C#, how do I convert this stored binary back to string hexadecimal value?

Question #3: I am using EF with a database-first approach. How do I query the SQL Server table for the matching objectid (considering Objectid is stored as varbinary)

 public vod DoWork(string mongoid) { //how do i get client based on `mongoid` parameter which is string? var result = _dbContext.Clients.SingleOrDefault(x=>x.MongoId == ?????) }

assume that MongoId is varbinary column

Have you considered storing the ObjectID as just a string i.e. '54aedb94e6c12b1c0e83385f' ? — Vince Bowdren
– Vince Bowdren, Commented Sep 7, 2016 at 12:57
that's how I am storing it currently. but then I read this article stackoverflow.com/questions/10978807/… — LP13
– LP13, Commented Sep 7, 2016 at 14:50

profesor79 · Accepted Answer · 2016-09-09 18:16:12Z

as this proposition will lead to increase storage footprint, as an outcome we will have a possibility to create multi-field index.

solution: add fields that will store extra info (Pid Timestamp Increment Pid)

var o = new ObjectId(); var o1 = o.Timestamp; //int var o2 = o.Increment; //int var o3 = o.Machine; //int var o4 = o.Pid; //short

and for particular objsctId we could have:

objectID: 57ce88b4857eda2fe48f042f Timestamp: 1473153204 Machine: 8748762 Pid: 12260 Increment: 9372719

Collectives™ on Stack Overflow

How to store mongo ObjectID into SQL Server?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related