CODEWITHSUNDEEP
c#mongodbentity-frameworkmongodb-.net-driver
Rohit
Rohit

Reputation: 231

How to map mongo bson array to c# object , then save in SQL using Entity framework

I have a mongodb bson collection with the following structure, the credentials[] is causing problem, can you please recommend a solution. I have device class and has navigation property for entity framework. context is also set, just the json array is causing problem since it has strings inside and not a document. Bson Document

{
"_id" : ObjectId("592dbf1446dbac785d6556a5"),
"deviceName" : "Computer1",

"updated" : ISODate("2017-06-30T20:18:25.681Z"),
"credentials" : [ 
    "fa4bafad-51b0-494c-89d8-6154f23bf3af", 
    "3138ac95-b9e6-4dbe-9825-f109c0d47f67"
],

"lastActivity" : ISODate("2017-06-06T17:55:35.894Z"),
"clientVersion" : "2.14.0"

}

C# classes

    public  class Device
{

    [Key]


    public string _id { get; set; }
    public string deviceName { get; set; }
    public DateTime? updated { get; set; }
    public  string clientVersion { get; set; }

    public  DateTime lastActivity { get; set; }
    public ICollection <Credentials> credentials { get; set; }


}



public class Credentials
{

    [Key]

    public Guid CredentialId { get; set; }

    public string credentials { get; set; }
    [ForeignKey("Device")]
    public string DeviceId { get; set; }
    public virtual Device Device { get; set; }


}

 using (var context = new GkDataContext())
        {

            var deviceCollection = await Db.GetCollection<Device>("Devices").Find(new BsonDocument()).ToListAsync();

            context.device.AddRange(deviceCollection);

            context.SaveChanges();


        }

Upvotes: 0

Views: 2081

Answers (1)

Steve Py
Steve Py

Reputation: 34908

Ok, so are your EF entities are looking at Mongo objects, or just at SQL Server table records?

Your Mongo entities (or objects) will just have the string collection. I'd look to add a non-mapped collection of GUIDs to the classes parsing the Mongo objects. To handle the entity references to a Credential table in SQL side I'd look to do a SelectMany against those IDs, then pre-load the Credential entities from SQL Server to provide the matching references to add to the SQL instance of Device.

public class MongoDevice
{
   ...
   public ICollection<string> Credentials{ get; set; }

   [NotMapped]
   public ReadOnlyCollection<Guid> CredentialIds
   {  // Can be optimized, but requires coding to keep in sync with operations against Credentials...
      get{ return Credentials.Select(x=> Guid.Parse(x)).ToList().AsReadOnly(); }
   }
 }

public class Device 
{
   ...
   public virtual List<Credential> Credentials{ get; set; }
}

You might have been thinking of having one entity that could be switched between Mongo & SQL? I don't see that being practical because where you want object/table references in SQL for Credentials, Mongo doesn't provide those.

After you load your Mongo entities and have your collection of credential IDs...

// If you have a lot of devices / credentials, break this up into several iterations. You don't want to end up with a huge IN clause below.
List<Guid> credentialIds = mongoDevices.SelectMany(x=> x.CredentialIds).ToList();
List<Credential> credentials = sqlDbContext.Credentials.Where(x=> credentialIds.Contains(x.CredentialId)).ToList();

Now when you transpose your Mongo Device over to an SQL record Device... The caviat above is that mongoDevices must be an in-memory collection by this point because EF won't know what CredentialIds is. It's merely a helper property to help with the parsing to Guid.

This can be done a number of ways, you might iterate over each Device (Mongo) and load/create the counterpart in SQL, then inspect the Credential IDs to ensure each is accounted for, or it could be processed all at once on the assumption that all records should be accounted for. that "credentials" collection above would have references to all credential records in SQL with the batch of IDs, fetched in one hit to the SQL Server rather than retrieving records on-demand. If this is expected to be huge #s of records then either break into batches or start with on-demand per record and work from there.

Upvotes: 0

Related Questions