To check the table configurations for EF Core 3.1, I did the following and it helped identify issues.

It iterates all tables in the context and selects one row. If there is an exception, it will catch it and display some related information. It might be helpful to also capture the stack trace.

    public string LoopAllTables(bool stopAfterFirstFail)
    {
        var exceptions = new StringBuilder();
        var propertyInfos = _dbContext.GetType().GetProperties().OrderBy(o => o.Name).ToArray();
        foreach (var propertyInfo in propertyInfos)
        {
            var property = propertyInfo.GetValue(_dbContext);
            if (property is IQueryable<object> tableQ)
            {
                try
                {
                    tableQ.Take(1).Load();
                }
                catch (Exception ex)
                {
                    exceptions.AppendLine(ex.Message + Environment.NewLine + "  from " + 
                       propertyInfo.Name);
                    if (stopAfterFirstFail)
                    {
                        break;
                    }
                }
            }
        }

I just used parallel tasks to decrease the time a process took from over an hour to about 2 minutes.  I was wary of creating so many database contexts, but it paid off in a major way!

See the snippet of code below to see how I used the Parallel.ForEach.  .Net created almost 100 threads during the execution.

Parallel.ForEach(qry, issue =>
{
	using (var _db = new WCO())
	{
		dobType dob;
		List<dobType> birthdays = new List<dobType>();

		foreach (
			var subIssue in
				issue.DOBValue.Trim().Split(new string[] { " TO ", " to ", "-", " - " },
					StringSplitOptions.RemoveEmptyEntries))
		{
			string valueToTest = subIssue;

			// Try Month Year regex
			if (tryDateFirstPart(valueToTest, out dob))
			{
				logger.Info(string.Format("Parsed {0} to Month: {1}, Day {2} Year: {3}", issue,
					dob.month,
					dob.day, dob.year));
				saveParseDOB(dob, issue, _db);
			}
			else
			{
				logger.Error(string.Format("Could not parse {0}", issue.DOBValue));
			}
		}

	}
});

If you need someone with my ability to decrease processing times on a full time basis, please contact me through LinkedIn 

Have a great day!

I downloaded some code a recently that creates an Oracle Database, but I don’t have Oracle, so I converted most of the code to SQL Server.  One of the harder things to convert was the column and table comments.  One of the features of Oracle that Microsoft SQL Server does not have directly is commenting. It appears that you can have comments on a table and comments on a column (field) in the table.

Well after doing some research into how to convert these to SQL Server,  I came across an article at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37796 that told how to do this.  Well, I had a script with over 1,100 comments, so I used LinqPad to create a small program that would convert my script using Regular Expressions.

Here is the code for that, please let me know if this helps anyone.  It sure made my work a lot easier.

This is for Table Comments:

void Main()
{
		List<string> lines = System.IO.File.ReadAllLines(@"C:\Users\username\Downloads\TableComments.SQL").ToList();

		// Display the file contents by using a foreach loop.
		Regex ex = new Regex(@"^comment on table (\w+) IS (\'.+\');", RegexOptions.IgnoreCase);
		StringBuilder sb = new StringBuilder();

		string repl = @"EXEC sp_addextendedproperty @name = N'MS_Description', @value = $2, @level0type = N'USER', @level0name = 'dbo',@level1type = N'Table', @level1name = '$1';";
		string newline = string.Empty;
		foreach (string line in lines)
		{
			if (ex.IsMatch(line))
			{
				newline = ex.Replace(line,repl);
				sb.AppendLine(newline);
				Console.WriteLine(newline);
			}
			else
			{
				// If there is not a match, output it anyway, might be comments in the code.
				sb.AppendLine(line);
				Console.WriteLine(line);
			}
		}

		// save the converted file.
		using (StreamWriter outfile = new StreamWriter(@"C:\Users\username\Downloads\TableCommentsConverted.SQL"))
		{
			outfile.Write(sb.ToString());
		}

}

And here is the code for Column comments.

void Main()
{
		string[] lines = System.IO.File.ReadAllLines(@"C:\Users\username\Downloads\ColumnComments.SQL");

		Regex ex = new Regex(@"^comment on column (\w+)\.(\w+) IS (\'.+\');", RegexOptions.IgnoreCase);
		StringBuilder sb = new StringBuilder();
		string repl = @"EXEC sp_addextendedproperty @name = N'$1.$2', @value = $3, @level0type = N'USER', @level0name = 'dbo',@level1type = N'Table', @level1name = '$1',  @level2type = N'Column', @level2name = '$2';";
		string newline = string.Empty;
		foreach (string line in lines)
		{

			if (ex.IsMatch(line))
			{
				newline = ex.Replace(line,repl);
				sb.AppendLine(newline);
				Console.WriteLine(newline);
			}
			else
			{
				// if there is not a match, output the line anyway, for spaces and comments in the file.
				sb.AppendLine(line);
				Console.WriteLine(line);
			}
		}

		// save the converted file.
		using (StreamWriter outfile = new StreamWriter(@"C:\Users\username\Downloads\ColumnCommentsConverted.SQL"))
		{
			outfile.Write(sb.ToString());
		}

}

// Define other methods and classes here