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).

 

Advertisements

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

}