LINQ query should not include your class projection to improve performance

Possibly save 1 hour of your time: I have found that performance of your LINQ query could be impact especially for large datasets when you project your results directly to a class object. It is better to project to .NET object after the sql query results came back first.

var results = (from a in context.TableA
                    join b in context.TableB on a.ID equals b.ID
                    select new YourClassName
                    {
                        Year = a.Year,
                        Name = b.Name
                    });
var list = results.ToList();

The code above is much slower than the code below for large datasets.

var results = (from a in context.TableA
                    join b in context.TableB on a.ID equals b.ID
                    select new
                    {
                        Year = a.Year,
                        Name = b.Name
                    });
var list = results.ToList();

var returnList = list.Select(x => new YourClassName
                {
                    Year = x.Year,
                    Name = x.Name
                }).ToList();

 

Advertisements

Request to run job refused because the job is already running from a request by user

Possibly save 1 hour of your time: I have a job that can take awhile to finish on development environment. I have started running the job and realize something I have missed. Now I want to stop or cancel the job, but I get this error telling me that the job is already running.

Solution: The easiest way is to restart the SQL Server Agent Service by going to SQL Server Configuration Manager or sqlservermanager11.msc in command prompt.

 

 

 

Float, Double, Decimal in C#

Possibly save 1 hour of your time: There are times where you have to do some calculations on your data in .NET. If the data are based on different data types such as float, double or decimal, you will get error when you do calculations and storing the result in a certain datatype.

The best is to find out which of these fields have the most precision required or basically what is the biggest number possible.

Float, Double, Decimal is the biggest from the left to right respectively.

In my case, there are some big numbers so I just store them as decimals in both .NET and SQL.

Here is a good explanation on how SQL datatypes map to .NET datatypes:

https://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

(SQL -> .NET)

float -> double

decimal -> decimal

 

Error: System.InvalidOperationException : The specified cast from a materialized ‘System.Double’ type to the ‘System.Decimal’ type is not valid.

This happens when my SQL datatype is float but in .NET the datatype is decimal. Since I need to do calculations using divide operator, I will change SQL datatype from float to decimal(30,15).

 

Web API – return huge dataset via many records

Possibly save 1 hour of your time: My Web API returns many records and it breaks after it returns some where above 2 million records.

I tried the following but it did not work. According to documentation, it only works if you are using the JavaScriptSerializer Class. The max value is based on Int32.

At this moment, I don’t have any solution yet. The only thing I can think of is that the consumer can hit the database directly if accessible rather than calling the API. Or break down the query on the API to smaller data sets and retrieve partition subsets and aggregate them again.

Of course, you have to reason whether to bring all data back to memory and store on server cache is gonna help with front end performance. This will also require huge memory footprint on the server.

If you don’t need all data at once and can do specific query to minimize the number of data records, this might also perform better rather than trying to deal with all data at once.

  [Test, MaxTime(90000)]
        public void GetLotsOfData_ViaHttpCallThroughWebApi()
        {
            string apiUrl = "https://yourapi.com/api/data";

            HttpClient client = new HttpClient();
            var response = client.GetAsync(apiUrl).ConfigureAwait(false);
            var results = response.GetAwaiter().GetResult();

            Assert.That(results.IsSuccessStatusCode);
        }

LINQ SQL – FULL OUTER JOIN

Possibly save 1 hour of your time: There is no equivalent of full outer join in LINQ that is same as SQL.

The solution is to do a left outer join and then a right outer join using DefaultIfEmpty and then use UNION to join these two results.

var leftouterjoin = (from items1 in table1
                    join items2 in table2
                    on new { items1.column1, items1.column2 } equals new { items2.column1, items2.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items1.column1, column2 = items1.column2, Value = items1.Value });

var rightouterjoin = (from items2 in table2
                    join items1 in table1
                    on new { items2.column1, items2.column2 } equals new { items1.column1, items1.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items2.column1, column2 = items2.column2, Value = items2.Value });

                var fullouterjoin = leftouterjoin.Union(rightouterjoin).ToList();

                var returnList = fullouterjoin.Select(x => new YourClass
                {
                    column1= x.column1,
                    column2= x.column2,
                    Value = x.Value,
                }).ToList();

If you have null values, your UNION may return duplicates. The key is to add Distinct() after your UNION().

There is a difference between SQL UNION (which performs an implicit DISTINCT) and LINQ Union (which required an explicit Distinct()).

LINQ System.NotSupportedException – The entity or complex type cannot be constructed in a LINQ to Entities query.

Possibly save 1 hour of your time: In LINQ to SQL syntax, you cannot project into your class directly. In the example below, we also show how to do joins on multiple columns using anonymous type.

You may get the following exception:

  • System.NotSupportedException : The entity or complex type ‘YourObject’ cannot be constructed in a LINQ to Entities query.

You cannot select new class from your class directly. You need to store result on a variable and execute the query and then use LINQ lambda syntax to project to your class.

 

var list = (from items1 in table1
join items2 in table2
on new { items1.column1, items1.column2} equals new { items2.column1, items2.column2}
select new { column1 = items1.column1, column2 = items1.column2, value = items2.Value }
).ToList();

var returnList = list.Select(x => new YourClass
{
column1= x.column1,
column2= x.column2,
Value = x.Value
}).ToList();

 

NUnit – test performance time on your method

Possibly save 1 hour of your time: There will be times you want to write some integration unit tests that hit the database to test performance.

One easy way to impose the maximum time in milliseconds on the unit test is using the MaxTime property available on NUnit and can be done as follows:

 [Test, MaxTime(2000)]
public void YourMethod()

Tip: You might also want to do an initial call to your database in OneTimeSetup to simulate first request will take longer time.

[OneTimeSetUp]
public void OneTimeSetUp()
{

// do a call to database to simulate that first request will take longer time

}

 

SSIS – File path is not valid – job failed on reading second file path

Possibly save 1 hour of your time: We have a SQL Server agent job that runs multiple SSIS packages. Each package is independent. Each package will process a data file that is located on another server. The data files for all packages are stored at same location. When the job runs, it always failed on the second package regardless of which data file it is looking at. So if step 1 ran, then step 2 will failed. The next time you run it, Step 2 will passed and Step 3 will failed on reading the file. The error will say the file path to the file is not valid.

Funny enough, when we give full administrator right to the identity running the job, all the steps passed.

We start thinking this was a permission issue so we check all the database permissions for this identity and could not find the issue.

Solution: We found out that there was a mapped share drive created. This was causing the issue. We use UNC path as environment variable to find the file. However the mapped share drive has the same beginning path like \\server\folder. Once we removed the mapped share drive, the job is reading subsequent file properly and behaving as expected.

SSIS – Could not find stored procedure sp_ssis_addlogentry

Possibly save 1 hour of your time: For some reason when the SSIS package was deployed in a different environment and when it ran, it gives this error.

The stored procedure is not automatically created.

Solution: In my case, the issue was actually related to the SQL Server Agent job not setup probably to tie to the environment variables. For some reason that causes this issue.

After the job steps have the environment variables linked, then job is running fine and the stored procedure was automatically created.

 

The type initializer for threw an exception static class

Possibly save 1 hour of your time: I got this error when I am creating unit test to test a static class.

The type initializer for ‘YourClassName‘ threw an exception.

You can debug and look at the details of the Inner Exception property.
In my case, I see this error.
{“Value cannot be null.\r\nParameter name: String”}

If you are still not sure, debug into your static class by putting a break point in the first line of the class.

My issue turns out that there was ConfigurationManager.AppSettings value missing in the app.config file.