LINQ Challenges and SQL Server Compact Edition

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.IO;
using System.Linq;
using System.Text;
using System.Reflection;

namespace TimeApp.DataAccess {

	public partial class TimeAppDataContext {

		public TimeAppDataContext() : this(ConnectionString) { }

		#region Properties

		/// <summary>
		/// Gets the connection string for the data file.
		/// </summary>
		/// <remarks>The data file path is stored in the registry but retrieved via the shared Options class.</remarks>
		protected static string ConnectionString {
			get {
				return string.Format("Data Source={0}", Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "TimeApp.sdf"));
			}
		}

		#endregion

		#region Projects

		/// <summary>
		/// Gets a summary of all projects from the DB.
		/// </summary>
		/// <returns></returns>
		public List<ProjectSummary> GetAllProjects() {
			ObjectTrackingEnabled = false;

			var ProjectList = from P in Projects
							  orderby P.Name
							  select new ProjectSummary {
								  ProjectId = P.ProjectId,
								  Name = P.Name,
								  IsBillable = P.IsBillable,
								  DetectAway = P.DetectAway
							  };
			return ProjectList.ToList();
		}

		/// <summary>
		/// Gets a summary of all projects and their tasks from the DB.
		/// </summary>
		/// <returns></returns>
		public List<ProjectSummary> GetAllProjectsAndTasks() {
			ObjectTrackingEnabled = false;

			// Pre-fetch the tasks for each project.
			DataLoadOptions Options = new DataLoadOptions();
			Options.LoadWith<Project>(P => P.Tasks);
			LoadOptions = Options;

			var ProjectList = from P in Projects
							  orderby P.Name
							  select new ProjectSummary {
								  ProjectId = P.ProjectId,
								  Name = P.Name,
								  IsBillable = P.IsBillable, 
								  DetectAway = P.DetectAway,
								  Tasks =
									   (from T in P.Tasks
										orderby T.Name
										select new TaskSummary {
											TaskId = T.TaskId,
											Name = T.Name,
											IsBillable = T.IsBillable, 
											DetectAway = T.DetectAway
										}).ToList()
							  };

			return ProjectList.ToList();
		}

		/// <summary>
		/// Gets a project by its ID.
		/// </summary>
		/// <param name="projectId">The ID of the project to get.</param>
		/// <returns></returns>
		public Project GetProject(int projectId) {
			Project Result = Projects.Single(P => P.ProjectId == projectId);
			return Result;
		}

		/// <summary>
		/// Deletes a project by its ID.
		/// </summary>
		/// <param name="projectId">The ID of the project to delete.</param>
		public void DeleteProject(int projectId) {
			Project EntityToDelete = Projects.Single(P => P.ProjectId == projectId);
			Projects.DeleteOnSubmit(EntityToDelete);
			SubmitChanges();
		}

		#endregion

		#region Tasks

		/// <summary>
		/// Get a summary of all tasks for the specified project ID from the DB.
		/// </summary>
		/// <param name="projectId">The ID of the project to get tasks for.</param>
		/// <returns></returns>
		public List<TaskSummary> GetTasksByProject(int projectId) {
			ObjectTrackingEnabled = false;

			var TaskList = from T in Tasks
						   where T.ProjectId == projectId
						   orderby T.Name
						   select new TaskSummary {
							   TaskId = T.TaskId,
							   Name = T.Name,
							   DetectAway = T.DetectAway
						   };

			return TaskList.ToList();
		}

		#endregion

		#region Activity Types

		/// <summary>
		/// Get a list of all activity types from the DB.
		/// </summary>
		/// <returns></returns>
		public List<ActivityType> GetAllActivityTypes() {
			var ActivityTypeList = from A in ActivityTypes
								   orderby A.Name
								   select A;

			return ActivityTypeList.ToList();
		}

		#endregion

	}

}