LINQ to SQL INSERT WHERE not in collection

Question

I need to mirror all of the DNS entries on our DNS server in a SQL database. I am able to query DNS using WMI and C#. And I can query SQL for existing records using L2S but there is a lot of room for improvement.

What does the LINQ To SQL statement look like that will insert records that are not in the ManagementObjectCollection returned by WMI into the SQL table?

What I have so far works but seems inefficient.

internal static void SyncRecordInfo()
        {
            int affected = 0;
            DnsEntityDataContext db = new DnsEntityDataContext();
            //Select records in DNS
            string[] Types = { "A", "CName", "MX" };

        for (int i = 0; i < Types.Length; i++)
        {
            string Query = "SELECT * FROM MicrosoftDNS_" + Types[i] + "Type";
            ObjectQuery qry = new ObjectQuery(Query);
            DnsProvider dns = new DnsProvider();
            ManagementObjectSearcher s = new ManagementObjectSearcher(dns.Session, qry);
            ManagementObjectCollection col = s.Get();
            dns.Dispose();

            foreach (var item in col)
            {
                //(from m in ManagementObjectCollection select m.SomeID).Contains(<main source field>)

                int count = (from r in db.Records
                              where ((r.Data.Contains(item["RecordData"].ToString())) && (r.Name.Contains(item["OwnerName"].ToString())))
                              select r).Count();

                if (count < 1)
                {
                    //Insert records in SQL
                    Record record = new Record();
                    record.Data = item["RecordData"].ToString();
                    record.Name = item["OwnerName"].ToString();
                    //record.Notes = item["ContainerName"].ToString();
                    record.Type = Types[i];

                    db.Records.InsertOnSubmit(record);
                    db.SubmitChanges();
                    affected++;
                }
            }
        }
    }

Answer 1

Hello,

where !(from m in ManagementObjectCollection select m.SomeID).Contains(<main source field>)

You use subquery/contains to check to see whether one exists in the collection or not. I don't know what you are comparing field-wise, so I can't tell you more details.

HTH.