none
LINQ and Except RRS feed

  • Question

  • I'm using Except to select all records (call them delta records) in one file (today's) that were not in another file (yesterday's). These files always grow by zero or more records each day and records are never removed but some may change and some may get added.

    The LINQ Except works fine however I also need to report the line number (in today's file) of each record we process from the delta records.

    I do this simply by calling todays.IndexOf(record) which works but has a 60% CPU cost in the app I'm writing. The files contains upwards of 75,000 records (yet the delta records may be just 2,000 or so).

    I tried creating a list of these record numbers once (at the same time I do the Except) and then getting the record number is just a matter of looking up the IndexOf on the much smaller list (2000 or so). But the CPU went up because we're doing - overall - more work in total.

    So I wanted to know is there anything like Except that can give me not just the records from A that are not in B but also the index of the record within A.

    Thanks

    Saturday, November 17, 2018 5:53 PM

All replies

  • Hi Captain,

    Thank you for posting here.

    For your question, you wanted to know is there anything like Except to solve your performance problems.

    I would suggest that you could store the data in the file content into the database and then use the SQL statement.

    Here is a simple example for your reference.

    using System;
    
    using System.Collections.Generic;
    
    using System.Collections.Specialized;
    
    using System.Data.SqlClient;
    
    using System.IO;
    
    using System.Linq;
    
    using System.Text;
    
    using System.Threading.Tasks;
    
    namespace test4
    
    {
    
        class Program
    
        {
    
            static void Main(string[] args)
    
            {
    
                string str= @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = test; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite; MultiSubnetFailover = False";
    
                DateTime dateTime = DateTime.Now;
    
                using (SqlConnection conn = new SqlConnection(str))
    
                {
    
                    conn.Open();
    
                    SqlCommand cmd = conn.CreateCommand();
    
                    cmd.CommandText = "select *from today except select *from yesterday";
    
                    SqlDataReader reader = cmd.ExecuteReader();
    
                    while (reader.Read())
    
                    {
    
                        string txt = reader.GetString(reader.GetOrdinal("txt"));
    
                        int index = reader.GetInt32(reader.GetOrdinal("id"));
    
                        Console.WriteLine(txt+"  " + index);
    
                    }
    
                }
    
                Console.WriteLine(DateTime.Now-dateTime);
    
                Console.ReadKey();
    
            }
    
            }
    
       
    
    }

    Best regards,

    Zhanglong



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 20, 2018 5:12 AM
    Moderator