Comparison to LINQ to SQL

Coordinator
Sep 22, 2008 at 2:45 AM
Here is a chunk of code that I did with both the DAC and LINQ to SQL so you can see a comparison.

Obviously the DAC code is pretty straight forward.  What it does is easily visible by anyone that opens up the corresponding SQL file, where as LINQ is visible in the code.

Eg The SQL for GetStatusIdByName is:
SELECT [StatusId]
FROM dbo.[Status]
WHERE [Name] = @StatusName

So far the only thing I've seen the DAC do better than LINQ to SQL is returning random results without a view/stored procedure.

Ie with the DAC you can write a query like:
SELECT TOP 10 [Files]
FROM [File]
ORDER BY NEWID()

There are probably other strengths and weaknesses, but I personally am not making heavy use of either atm.

=======================================================
The code:
=======================================================
using System;
using System.Data.Linq;
using System.IO;
using System.Linq;
using Destra.Common.CommandLine;
using Destra.Common.ProductionEnvironment;
using FileServiceSizeChecker.DataAccess;

namespace FileServiceSizeChecker
{
    class Program
    {
        private const string pendingDelete = "PendingDelete";
        private static int batchSize = 1000;
        private static readonly DateTime checkFilesSince = DateTime.UtcNow.AddMonths(-3);

        static void Main(string[] args)
        {
            var appArgs = new Arguments(args);
            if (appArgs["help"] != null || appArgs["usage"] != null || appArgs["?"] != null)
            {
                PrintUsage();
                return;
            }
            else
            {
                if (appArgs["BatchSize"] != null)
                {
                    batchSize = int.Parse(appArgs["BatchSize"]);
                }
                if (appArgs["CodeBase"] != null && appArgs["CodeBase"].ToUpperInvariant() == "DAC")
                {
                    DACMethod(appArgs["SystemName"], appArgs["SystemId"], appArgs["DoAllFiles"]);   
                }
                else
                {
                    LINQMethod(appArgs["SystemName"], appArgs["SystemId"], appArgs["DoAllFiles"]);                   
                }               
            }
        }

        private static void PrintUsage()
        {
            Console.WriteLine("Usage: FileServiceSizeChecker [-SystemName SystemToCheck | -SystemId SystemIdToCheck] [-DoAllFiles true/FALSE]");
        }
       
        private static void DACMethod(string systemName, string sSystemId, string doAllFiles)
        {
            int? systemId = null;

            if (!string.IsNullOrEmpty(sSystemId))
            {
                int tempId;
                if (int.TryParse(sSystemId, out tempId))
                {
                    systemId = tempId;
                }
            }
            if (!string.IsNullOrEmpty(systemName) && !systemId.HasValue)
            {
                var dac = new GetSystemIdByName();
                try
                {
                    systemId = dac.ExecuteScalar(systemName);
                }
                catch (NullReferenceException)
                {
                    // Ignore this.  DAC needs to be updated to handle nulls for ExecuteScalar
                }
               
            }
            bool checkAllFiles = false;
            if (!string.IsNullOrEmpty(doAllFiles))
            {
                bool.TryParse(doAllFiles, out checkAllFiles);
            }
            bool foundFiles = false;
            do
            {
                // Get a chunk of files and check their sizes  
                var files = GetBatchOfFilesToCheck.ReadEntities(batchSize, systemId, checkFilesSince);
                if (files.Length > 0)
                {
                    foundFiles = true;
                }
                foreach (var file in files)
                {
                    string filePath = PathTranslation.ConvertToTrustedZonePath(file.FilePath);
                    if (System.IO.File.Exists(filePath))
                    {
                        var info = new FileInfo(filePath);
                        file.FileSize = info.Length;
                    }
                    else
                    {
                        var getStatusIdByName = new GetStatusIdByName();
                        file.StatusId = getStatusIdByName.ExecuteScalar(pendingDelete);
                    }
                    var updateFile = new UpdateFile();
                    updateFile.Execute(file);
                }

            } while (checkAllFiles && foundFiles);
        }

        private static void LINQMethod(string systemName, string sSystemId, string doAllFiles)
        {
            var db = new FileDataContext();

            int? systemId = null;

            if (!string.IsNullOrEmpty(sSystemId))
            {
                int tempId;
                if(int.TryParse(sSystemId, out tempId))
                {
                    systemId = tempId;
                }
            }
            if (!string.IsNullOrEmpty(systemName) && !systemId.HasValue)
            {
                var system = from fileClient in db.FileClients
                             where fileClient.Name == systemName
                             select fileClient.SystemId;
                systemId = system.First();
            }
            bool checkAllFiles = false;
            if(!string.IsNullOrEmpty(doAllFiles))
            {
                bool.TryParse(doAllFiles, out checkAllFiles);
            }
            bool foundFiles = false;
            bool systemIdHasValue = systemId.HasValue;
            if (!systemId.HasValue)
            {
                systemId = 0;
            }
            do
            {
                // Get a chunk of files and check their sizes  
                var files = from file in db.Files
                            where (!file.FileSize.HasValue || file.FileSize.Value == 0)
                                  && file.Status.Name == "Live"
                                  && (!systemIdHasValue || file.SystemId == systemId.Value)
                                  && (!file.LastCleanupCheck.HasValue || file.LastCleanupCheck < checkFilesSince)
                            select file;
                           
                if (files.Count() > 0)
                {
                    foundFiles = true;
                }
                foreach (var file in files.Take(batchSize))
                {
                    string filePath = PathTranslation.ConvertToTrustedZonePath(file.FilePath);
                    if (System.IO.File.Exists(filePath))
                    {
                        var info = new FileInfo(filePath);
                        file.FileSize = info.Length;
                    }
                    else
                    {
                        var getStatusIdByName = from status in db.Status
                                                where status.Name == pendingDelete
                                                select status.StatusId;
                        file.StatusId = getStatusIdByName.First();
                    }
                    file.LastCleanupCheck = DateTime.UtcNow;
                }
                db.SubmitChanges(ConflictMode.ContinueOnConflict);
            } while (checkAllFiles && foundFiles);
        }
    }
}