Click here to Skip to main content
13,096,858 members (82,749 online)
Click here to Skip to main content

Stats

151.3K views
1.1K downloads
165 bookmarked
Posted 21 Dec 2007

LINQ Challenges and SQL Server Compact Edition

, 25 Mar 2008
Overcoming challenges with LINQ to SQL and using LINQ with 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

	}

}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Matt Sollars
Web Developer
United States United States
I began programming on my Commodore 64 at around the age of 12. After migrating to DOS and then Windows, I decided to take on the Web. Several languages and platforms later, I have settled in with .NET nicely. I am currently the owner of a software consulting company and lead application developer for a learning-based technology consultation company.

The love of a finished application is usually at war with the desire to improve it as soon as it's released (they're never really finished).

You may also be interested in...

Pro
Pro
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 25 Mar 2008
Article Copyright 2007 by Matt Sollars
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid