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

Advertisements

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.

T-SQL – split a string value based on comma or another delimiter

Possibly save 1 hour of your time: This shows how you can take a column that has data such as a string value with comma separated value or semi-colon separated value and process each value for other purpose. In my case I want to check for foreign key not exist.

</pre>
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT [column_with_delimiter_separated_values]
FROM [dbo].[YourTable]

DECLARE @textString varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @textString

DECLARE @tempSplitText TABLE
(
SplitString nvarchar(500)
)

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @delimiter char = ';'
DECLARE @pos INT = 0
DECLARE @len INT = 0
DECLARE @value varchar(500)

WHILE CHARINDEX(@delimiter, @textString, @pos+1)>0
BEGIN
set @len = CHARINDEX(@delimiter, @textString, @pos+1) - @pos
set @value = SUBSTRING(@textString, @pos, @len)
INSERT INTO @tempSplitText(SplitString) VALUES(RTRIM(LTRIM(@Value)))
set @pos = CHARINDEX(@delimiter, @textString, @pos+@len) +1
END

IF (LEN(@region) > @len) --deals with last element
BEGIN
set @value = SUBSTRING(@textString, @pos, LEN(@textString)-@pos+1)
INSERT INTO @tempSplitText(TextString) VALUES(RTRIM(LTRIM(@Value)))
END

FETCH NEXT FROM @MyCursor
INTO @textString
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

select r. from @tempSplitText r
left join [dbo].[YourTableWithTheForeignKey] c on r.SplitString = c.ForeignKey
where c.ForeignKey is null
<pre>

 

SSIS – no column information was returned by the sql command

Possibly save 1 hour of your time: In SSIS, I have a data flow task that selects the source with some logic using table variable. It causes this compilation error when you try to save.

SSIS doesn’t know like using table variable in the SQL command.

Solution:

I have some complex logic so I need to use variable table. Temp table would most likely have same issue. So I just use a stored procedure instead. In my SQL command I have exec MyStoredProcedureName.

Using T-SQL cursor to loop through table records

Possibly save 1 hour of your time: There may be times you need to loop through each record in a table and process the data in the specific row or column.

DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
  SELECT column1, column2
  FROM table
  WHERE clause

DECLARE @column1 int;
DECLARE @column2 varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @column1 , @column2

WHILE @@FETCH_STATUS = 0
BEGIN
 print Cast(@column1 as varchar(10)) + '-' + @column2
 FETCH NEXT FROM @MyCursor
INTO @column1 , @column2
END

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

SSIS – How to setup parameter for Flat File Connection Manager connection string

Possibly save 1 hour of your time: This shows how you can add an expression to use as parameter for the Flat File Connection Manager or any other ones.

Right click on your connection manager and choose Properties. The properties window will show up. Find the Expressions and then click on the ellipsis on the right.

In the Property Expressions Editor that opens, you can now choose different Property such as ConnectionString. In my case, I set it to a user variable that tells me where the error log file is.

expression parameter