20230919 .NET interview experience

SQL

Main difference between IQuerable and IEnumerable?

https://stackoverflow.com/questions/252785/what-is-the-difference-between-iqueryablet-and-ienumerablet

So the difference between IQueryable and IEnumerable is about where the filter logic is executed. One executes on the client side and the other executes on the database.

So if you work with only in-memory data collection, IEnumerable is a good choice, but if you want to query data collection which is connected with database IQueryable is a better choice as it reduces network traffic and uses the power of SQL language.

Id name, delete duplicate data

Leetcode original title https://leetcode.cn/problems/delete-duplicate-emails/

DELETE p1
 FROM Person p1, Person p2
 WHERE
     p1.Email = p2.Email AND p1.Id > p2.Id
 

Suppose ID 1, 3, 5 have the same email
1 – 1
1 – 3
1 – 5
Finally 1 is left

3 – 1
3 – 3
3 – 5
3 > 1 deleted 3

5 – 1
5 – 3
5 – 5
5 > 1, 5 > 3 deleted 5

-- Cannot run on MySQL, but can run on SQL Server
 DELETE FROM A
 WHERE id NOT IN (
     SELECT MIN(id) -- retain the rows with the smallest id value in the name column
     FROM A
     GROUP BY name
 )
 

Load columns from one table into another table

Insert the s2 and s3 columns of the row containing ‘abc’ in column s1 of table A into table B as columns s1 and s2

INSERT INTO B (s1, s2)
 SELECT s2, s3 FROM A
 WHERE s1 LIKE '%abc%';
 

select * A, how many rows and rows of B?

Row: rowA * rowB, column: rowA + rowB

Cartesian product:

Optimistic Lock

Optimistic locking is a concurrency control mechanism used to solve data inconsistencies that may occur when multiple threads modify the same data at the same time. A common way to implement optimistic locking is through a version control mechanism. The following is a common implementation method:

1. Add version field: In the data table that requires concurrency control, add a field to record the version number. This field can be of type integer or timestamp.
2. Read data and version number: When reading data, get the version number of the current data at the same time.
3. Modify data: When you want to modify the data, first check whether the currently held version number is consistent with the latest version number. This can be done by comparing the version number of the data to be modified with the version number obtained when reading.
4. Optimistic lock conflict handling:

   • If the version numbers are consistent, it means that no other thread has modified the data, you can perform modification operations and update the version number.

   • If the version numbers are inconsistent, it means that the data has been modified by other threads, and there may be a concurrency conflict. You can choose to cancel the current operation, retry the operation, or perform other related processing logic.

Inference question

100 lights

There are 100 lights in the hall, and each light is numbered, ranging from 1-100. Each light is controlled by a switch. (Press the switch once to turn the light on, and press it again to turn off the light. The number of the switch is the same as the controlled light.) At the beginning, all the lights are off. Now press the switch according to the following rules.
The first time, turn on all the lights.
The second time, press all switches that are multiples of 2.
The third time, press all switches that are multiples of 3.
And so on. For the Nth time, press all switches that are multiples of N.
After pressing the button for the 100th time, how many lights are still on in the hall?

Write it down in sequence, and you will find that the steps in which each lamp is operated are actually factors of the lamp number.
Steps if lamp 36 is operated:

(1, 36), (2, 18), (3, 12), (4, 9), (6, 6), (9, 4), (12, 3), (18, 2) (36, 1)

At this time, the question is transformed into finding the number of numbers with odd factors among 100 numbers [1-100].

ref: https://www.cnblogs.com/dhf327/p/4773672.html

We noticed that the process of finding the factors of a number starts from 1 to its square root. Except for the square root, other factors appear in pairs, that is, only square numbers have an odd number of factors!

There are only 10 square numbers within 100, namely 1, 4, 9, 16, 25, 36, 49, 64, 81, 100, so the final result of the above question is that 10 lights are on at the end.�.

Programming question

How to implement a customizable collection that can be foreached

• Create a collection class that implements the GetEnumerator method of the IEnumerable interface to return an iterator

• Create an iterator class to implement the MoveNext, Reset, and Current methods of the IEnumerator interface

• foreach:

  var enumerator = collection.GetEnumerator();
   while (enumerator.MoveNext())
   {
   Console.WriteLine(enumerator.Current);
   }
   

// Custom collection class
 public class MyCollection : IEnumerable
 {
     private object[] items;

     publicMyCollection()
     {
         items = new object[3];
         items[0] = "Apple";
         items[1] = "Banana";
         items[2] = "Orange";
     }

     // Implement the GetEnumerator() method of the IEnumerable interface
     public IEnumerator GetEnumerator()
     {
         return new MyEnumerator(items);
     }
 }

 // Custom enumerator class
 public class MyEnumerator : IEnumerator
 {
     private object[] items;
     private int position = -1;

     public MyEnumerator(object[] collection)
     {
         items = collection;
     }

     // Implement the MoveNext() method of the IEnumerator interface
     public bool MoveNext()
     {
         position++;
         return (position < items.Length);
     }

     // Implement the Reset() method of the IEnumerator interface
     public void Reset()
     {
         position = -1;
     }

     // Implement the Current property of the IEnumerator interface
     public object Current
     {
         get { return items[position]; }
     }
 }
 

What is the output of the following code?

[TestClass]
 public class InheritanceTest
 {
     [TestMethod]
     public void test()
     {
         A a = new A();
         B b = new B();
         a.Func2(b);
         b.Func2(a);
     }

     class A
     {
         public virtual void Func1(int i)
         {
             Console.WriteLine(i);
         }

         public void Func2(A a)
         {
             a.Func1(1);
             Func1(5);
         }
     }

     class B : A
     {
         public override void Func1(int i)
         {
             base.Func1(i+1);
         }

         public void Func2(A a)
         {
             a.Func1(1);
             Func1(5);
         }
     }
 }
 /*
 2
 5
 1
 6
 */
 

In fact, it is an examination of polymorphism. The actual type of an object is not determined by the interface/base class type that “interprets” it, but by the object created when memory is allocated (new).