Entity Framework - How to migrate when there are two database contexts in your solution

Here I will explain how to migrate when there are two separate db contexts in two separate projects in the same solution.

The format for migration commands are as follows:

enable-migrations -ContextTypeName <DbContext-Name-with-Namespaces> -MigrationsDirectory:<Migrations-Directory-Name>

DbContext-Name-with-Namespaces - This is the full path to the database context class (including namespace)

Add-Migration -configuration <DbContext-Migrations-Configuration-Class-with-Namespaces> <Migrations-Name>

DbContext-Migrations-Configuration-Class-with-Namespaces - This is the full path to the configuration class (including namespace), which was created in the previous step.

Update-Database -configuration <DbContext-Migrations-Configuration-Class-with-Namespaces> -Verbose

Example:

1) In the Nuget Package Manager Console, make sure to select the "Default Project". That is the project which the  migrations is going to happen.

2) Also if the startup project contains a App.Config, you need to add the ConnectionString 's for the two context there as well.

3) Enable migrations

PM> Enable-migrations -ContextTypeName:My.Notification.ServicesMock.Data.MyMockContext -MigrationsDirectory:MyMigrations 
Result:
Checking if the context targets an existing database...
Code First Migrations enabled for project My.Notification.ServicesMock.

Now you will see a migrations directory been created in your project as "MyMigrations", which contains the configuration class. Later on, the migrations will be added to that folder.

4) Add Migration

PM> Add-Migration -configuration My.Notification.ServicesMock.MyMigrations.Configuration "Initial"
Result:
Scaffolding migration 'Initial'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration Initial' again.

5) Update database

PM> Update-Database -configuration My.Notification.ServicesMock.MyMigrations.Configuration -verbose
Result:
Using StartUp project '...'.
Using NuGet project '...'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.

Applying explicit migrations: [61401174_Initial].
Running Seed method.

Your migrations  are complete now. Have a look in the Db.

MOQ Framework - For test driven development

Install Moq using the Nuget package manager.

The dll will be added to you references.

Add the using directive:

using Moq;

Now you can mock your services using the Moq framework.

[Test]
public void MyClass_TestThisScenario_ShouldGetThisResult()
{

//declaration of the service in your unit test case
var mockPassengerService = new Mock<IPassengerService>();


//calling the method which I need to mock. Here it's the GetPassengers() method
mockPassengerService.Setup(a => a.GetPassengers(category,
new DateTime(2019, 08, 16, 14, 00, 00),
mappingList)).Returns(GetMultiplePassengers);

//my second moq example, here I'm sending any string value to the method to be mocked
var mockMailService = new Mock<IMailService>();
mockMailService.Setup(a => a.SendMail(It.IsAny<string>(), It.IsAny<string>()));

}

//returns the results for the mocked class
private List<Passenger> GetMultiplePassengers
{
     return new List<Passenger>()
    {
         new Passenger()
         {}
     };
}

NUnit the ease of Setup and TearDown Attributed methods

In NUnit there are two attributes [SetUp] and [TearDown] which eases the whole process of testing many test methods. The Setup attributed method will run before each unit test is run, and the TearDown attibuted method will run after each unit test. This eases the whole process of running all the test cases at once. A Sample NUnit test fixture wuold look like this:

[TestFixture]
class BhrPassengerServiceTest
{
[SetUp]
public void Before_Each()
{
using (var context = new ApplicationDbContext())
{
context.Database.ExecuteSqlCommand("DELETE FROM TableOne");
context.Database.ExecuteSqlCommand("DELETE FROM TableTwo");
context.Database.ExecuteSqlCommand("DELETE FROM TableThree");

context.Areas.Add(AreaOne);
context.Areas.Add(AreaTwo);
context.SaveChanges();
}
TimeService.Init();
}

[TearDown]
public void Teardown()
{
}

[Test]
public void MyClassToBeTested_TestScenarioOne_ShouldGetThisResult()
                {
                }

                 private IList<string>  HelperMethodOneUsedWithInTestMethod()
                 {
                 }
         }

How DbFunctions in Linq expression is evaluated in SQL server

var sent = _context.IsosPassengerNotifications.Any(n => n.Guid == notification.Guid &&
                                           n.DeviceRegistrationId == notification.DeviceRegistrationId &&
   DbFunctions.DiffSeconds(n.CreatedAt, TimeService.UtcNow) < 3);

when you hover over the variable in during debug time you get the SQL command for the linq query

The SQL Equivalent:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Guid] AS [Guid], 
    [Extent1].[DeviceRegistrationId] AS [DeviceRegistrationId], 
    [Extent1].[DeviceType] AS [DeviceType], 
    [Extent1].[DeviceLanguage] AS [DeviceLanguage], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[CreatedAt] AS [CreatedAt], 
    FROM [dbo].[Notifications] AS [Extent1]
    WHERE (([Extent1].[Guid] = '2019-08-16/356889/977010') OR (([Extent1].[Guid] IS NULL) AND ('2019-08-16/356889/977010' IS NULL))) AND
   (([Extent1].[DeviceRegistrationId] = '01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ') OR (([Extent1].[DeviceRegistrationId] IS NULL) AND ('01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ' IS NULL))) AND
    ((DATEDIFF (second, [Extent1].[CreatedAt], '2019-09-05 10:40:28.727')) < 3)

So how does DBFunctions.DiffSeconds work. It simply converts to a DATEDIFF in SQL server.

So how does the DATEDIFF work?

DATEDIFF simply gets the second parameter and substracts it from the first parameter.
Here you might get a positive or negative value depending on the parameters passed. To examine the subtraction result you could query the database as below

SELECT DATEDIFF (second, [Extent1].[CreatedAt], '2019-09-05 10:20:28.727')
    FROM [dbo].[Notifications] AS [Extent1]
    WHERE (([Extent1].[Guid] = '2019-08-16/356889/977010') OR (([Extent1].[IsosGuid] IS NULL) AND ('2019-08-16/356889/977010' IS NULL))) AND
   (([Extent1].[DeviceRegistrationId] = '01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ') OR (([Extent1].[DeviceRegistrationId] IS NULL) AND ('01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ' IS NULL)))


Or else simply try the following query and try changing the parameter values

select DATEDIFF (second, '2019-09-05 10:20:28.727', '2019-09-05 10:21:28.727')

Scheduling Tasks In ASP.NET With Quartz.Net

1) Install Quartz.Net using the Nuget Package manager.

2) Create a job which you would call upon to do your work. In next step this job will be called when the job starts its cycle.

    public class IsosJob : IJob
    {
        public void Execute(IJobExecutionContext context)
        {
            XmlDocument isosResult = new XmlDocument();
            isosResult.Load("http://path to get my xml content file");
            string path = "C:\\temp\\doc\\test.xml";
            XmlTextWriter writer = new XmlTextWriter(path, null);
        }
    }

3)  Creating a trigger and scheduling a job

using Quartz;
using Quartz.Impl;

namespace TestWeb.Jobs
{
    public class JobScheduler
    {
        public static void Start()
        {
            IScheduler scheduler = StdSchedulerFactory.GetDefaultScheduler();
            scheduler.Start();

            IJobDetail job = JobBuilder.Create<IsosJob>().Build();

            ITrigger trigger = TriggerBuilder.Create()
                .WithDailyTimeIntervalSchedule(s => s.WithIntervalInMinutes(2)
                .OnEveryDay()
                .StartingDailyAt(TimeOfDay.HourAndMinuteOfDay(14, 24))
             )
             .Build();

            scheduler.ScheduleJob(job, trigger);
        }
    }
}

4) Start the job

The best place to start the job is inside the Application_Start method in the global.asax file. The job will also start with along with other bootstrapping functions start.

        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            GlobalConfiguration.Configure(WebApiConfig.Register);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            JobScheduler.Start();
        }

Using this approach is good when you don't have access to various scheduling options in the server such as task scheduler or a window service. In those scenarios this comes in handy. That said, there are limitations in using web based approaches to carry on scheduled tasks unlike using windows services or a worker. For an instance, if the app pool gets recycled while the job is running the state of the application is not saved and information loss may happen.

Log4Net - Omitting the logs from the Root and appending to a different log file

I have set a root level logger which basically logs all.
There is a requirement to append a 3rd party information in a separate log file. Initially in the program, this would get logged even if you create a separate logger in the code, as the root captures all logging information.

        private static readonly ILog Logger
            = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        private static readonly ILog ThirdPartyLogger = LogManager.GetLogger("ThirdPartyLogger");
...
...
...
ThirdPartyLogger.Info($"This gets logged in the main log too, even though the appender is not set in the root");

The trick to omit it from the root level logger is to have the "additivity" attribute set in the logger.

    <appender name="ServiceAppenderInfo" type="log4net.Appender.RollingFileAppender">
      <file value="C:\ProgramData\Service\ServiceLog_Info.log" />
      <encoding value="utf-8" />
      <appendToFile value="true" />
      <rollingStyle value="Date" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="20" />
      <maximumFileSize value="5MB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date %message%n" />
      </layout>
      <filter type="log4net.Filter.LevelRangeFilter">
        <levelMin value="DEBUG" />
        <levelMax value="INFO" />
      </filter>
    </appender>

    <appender name="ServiceAppenderError" type="log4net.Appender.RollingFileAppender">
      <file value="C:\ProgramData\Service\ServiceLog_Error.log" />
      <encoding value="utf-8" />
      <appendToFile value="true" />
      <rollingStyle value="Date" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="5MB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date %message%n" />
      </layout>
      <filter type="log4net.Filter.LevelRangeFilter">
        <levelMin value="WARN" />
        <levelMax value="ERROR" />
      </filter>
    </appender>

    <appender name="ThirdPartyAppenderInfo" type="log4net.Appender.RollingFileAppender">
      <file value="C:\ProgramData\Service\ThirdParty_Info.log" />
      <encoding value="utf-8" />
      <appendToFile value="true" />
      <rollingStyle value="Date" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="5MB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date %message%n" />
      </layout>
      <filter type="log4net.Filter.LevelRangeFilter">
        <levelMin value="DEBUG" />
        <levelMax value="INFO" />
      </filter>
    </appender>

    <root>
      <level value="ALL" />
      <appender-ref ref="ServiceAppenderInfo" />
      <appender-ref ref="ServiceAppenderError" />
    </root>
    <logger name="ServiceLogger">
      <level value="INFO" />
      <appender-ref ref="ServiceAppenderInfo" />
      <appender-ref ref="console"/>
    </logger>
    <logger name="ThirdPartyLogger" additivity="false">
      <level value="INFO" />
      <appender-ref ref="ThirdPartyAppenderInfo" />
    </logger>

Entity Framework - Adding Auto Increment Column [Code First]

Use the attribute "DatabaseGenerated"

Example:

    public class Device
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int DeviceId { get; set; }
        public string Language { get; set; }
        public string RegistrationId { get; set; }
        public string DeviceType { get; set; }
        public int UserNo { get; set; }
    }

querying services with ARC (Advanced REST client)

Install ARC in Chrome

Add header details

Add body details

Using AutoMapper - Map from one object to another object C#

1) Install the automapper to your project through NuGet package manager

2) Initialize the automapper in the Global.asax file

        protected void Application_Start()

        {

            AutoMapper.Mapper.Initialize(cfg => cfg.AddProfile<AutoMapperProfile>());

         }

3) My classes to be mapped are:

   //DAO classes

    public class Passenger

    {

        public int OrderNo { get; set; }

        [Key]

        public int TcNo { get; set; }

        public string PsgrName { get; set; }

        public string FlightId { get; set; }

        public string CarrierCode { get; set; }

        public string FlightNo { get; set; }

        public string Origin { get; set; }

        public string Destination { get; set; }

        public string DepDateTime { get; set; }

        public string ArrDateTime { get; set; }

        public virtual ICollection<Device> Devices { get; set; }

    }

    public class Device

    {

        public string Language { get; set; }

        [Key]

        public string RegistrationId { get; set; }

        public string DeviceType { get; set; }

        public int TcNo { get; set; }

    }

//DTO classes to be mapped

    public class PassengerDTO

    {

        public int OrderNo { get; set; }

        public int TcNo { get; set; }

        public string PsgrName { get; set; }

        public string FlightId { get; set; }

        public string CarrierCode { get; set; }

        public string FlightNo { get; set; }

        public string Origin { get; set; }

        public string Destination { get; set; }

        public string DepDateTime { get; set; }

        public string ArrDateTime { get; set; }

        public virtual ICollection<DeviceDTO> Devices { get; set; }

    }

    public class DeviceDTO

    {

        public string Language { get; set; }

        public string RegistrationId { get; set; }

        public string DeviceType { get; set; }

    }

4) Create a mapper profile to map between the DAO and DTO object

    public class AutoMapperProfile : Profile

    {

        public AutoMapperProfile()

        {

            CreateMap<Passenger, PassengerDTO>();

            CreateMap<Device, DeviceDTO>();

        }

    }

5) Do the actual mapping in the code

IList<PassengerDTO> data = db.Passengers.Where(p => p.FlightId == FlightId).ProjectTo<PassengerDTO>().ToList();

SvcUtil to generate Proxy from the WSDL file

To generate the proxy from the wsdl file, use the svcutil.exe. You can access it either by navigating via command prompt to the physical path of the file (Example: C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin)  or simply by using Visual Studio Command Prompt

svcutil.exe ProvideShopping.wsdl  /Language=c# /t:Code /out:Shopping.cs /config:Shopping.config

You might get an error in case you don't specify the xsd files along with the WSDL file

Example errors might be:

Error: Cannot import wsdl:portType
Detail: An exception was thrown while running a WSDL import extension: System.ServiceModel.Description.dataContractSerializerMessageContractImporter
Error: Schema with target namespace "http://someUrl" could not be found 

Error: Cannot import wsdl:binding
Detail: There was an error importing a wsdl:portType that the wsdl:binding is dependent on.

Add the related .xsd files to the folder which contains the wsdl and run command again

svcutil.exe ProvideShopping.wsdl commontypes.xsd ShoppingQ.xsd ShoppingS.xsd edist.xsd bis.xsd structures.xsd System1.xsd  /Language=c# /t:Code /out:Shopping.cs /config:Shopping.config

Please note if you are not interested in going through the generated code, but you only want a service reference in Visual Studio, then simply provide the path to the wsdl file location in your computer. There also you have to have the related .xsd files in the folder containing the wsdl or else you will get an error when Visual Studio generates the service reference for you.

Log4Net - Logging to the Database and Troubleshooting logging Issues

There are a three parts to log4net. There is the configuration, the setup, and the call

There are seven logging levels, five of which can be called in your code.

OFF - nothing gets logged (cannot be called)
FATAL
ERROR
WARN
INFO
DEBUG
ALL - everything gets logged (cannot be called)

1) Setting up log for net in your project

Select Tools menu > NuGet package manager > Manage NuGet packages for the solution

Once the NuGet is installed you get the log4net dll under the References list.

2) Add the below line to the AssemblyInfo.cs file

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

Configurations

•  In The App.config file add the log4net configurations

<configuration>

  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"></section>
  </configSections>

• Adding the appender and the logger details.

  <log4net>
    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
            <bufferSize value="1" />
            <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
            <connectionString value="data source=MyDatabaseServer;initial catalog=MyDatabaseName;integrated security=false;persist security info=True;User ID=sa;Password=****" />
            <commandText value="INSERT INTO Log ([logdate],[message]) VALUES (@logdate, @message)" />
            <parameter>
              <parameterName value="@logdate" />
              <dbType value="DateTime" />
              <layout type="log4net.Layout.RawTimeStampLayout" />
            </parameter>
          <parameter>
          <parameterName value="@message" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
          </layout>
        </parameter>
    </appender>
    <logger name="AdoNetAppender">
      <level value="INFO" />
      <appender-ref ref="AdoNetAppender" />
    </logger>
  </log4net>

In the code, declare the logger

private static ILog myLog = LogManager.GetLogger("AdoNetAppender");

In the code add logging

myLog.Info(string.Format("{0}\t{1}\t{2}", DateTime.Now, "Before do work", i));

The logger pushes logging info to the DB in batches. So maker sure to push each log by using the <bufferSize value="1" /> as configured in the app.config above.

Trouble shooting Logger issues

Example 1 : Logger does not log anything to the database

To troubleshoot logger issues add the below configurations to the app.config file.

     <appSettings>
      <add key="log4net.Internal.Debug" value="true"/>
   </appSettings>

  <system.diagnostics>
        <trace autoflush="true">
        <listeners>
            <add
                name="textWriterTraceListener"
                type="System.Diagnostics.TextWriterTraceListener"
                initializeData="C:\tmp\log4net.txt" />
        </listeners>
    </trace>
  </system.diagnostics>

This will log issues in the mentioned text file.

Example 2 : After the DB goes offline or losing DB connectivity and comes back online, the logger does not Log anymore.

to solve this add the tag  <reconnectonerror value="true" />

Example:

    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
            <bufferSize value="1" />
            <reconnectonerror value="true" />

Adding this might slow down the app performance since, log4net trys to reconnect to the server till the connection attempt time outs.

To overcome that you need to set a connection timeout in the connection string as shown below:

 <connectionString value="data source=MyDatabaseServer;initial catalog=MyDatabaseName;integrated security=false;persist security info=True;User ID=sa;Password=****;Connect Timeout=1" />

If you had the troubleshooting configurations as shown in an above step, then you would get an output similar to this:

Install Uninstall Windows Service

From start menu select the "Developer Command Prompt for Visual Studio". right click on the menu item and run as administrator.

In the command prompt change the working directory to the bin/Debug folder of the Windows service Solution. Then give the command to install.

To install:

 installutil.exe MyWindowsService.exe

To uninstall:

installutil.exe /u MyWindowsService.exe