Category Archives: LINQ

Copy a SQL Record with LINQ Using Object Shallow Copy

.NET, c#, code, general, LINQ, microsoft, sql

I wish I had kept my reference material on this, so if anyone recognizes borrowed code here, please let me know…

The basic idea is, I needed to “copy” a SQL record within the same table, avoiding some fields. With SQL this could be accomplished with something like:

INSERT INTO ShippingInfo (Name,Value) SELECT Name,Value FROM ShippingInfo where id=5

But imagine there are 20 fields or so. I couldn’t find a native way to do this with LINQ. What I came up with is an object shallow copy with the ability to punch out (omit) fields by name. Here’s the method:

public static class AnyObject<t> where T : class, new()
{
    public static T ShallowCopy(T item)
    {
        return ShallowCopy(item, "");
    }
    
    public static T ShallowCopy(T item, string OmitPropeties)
    {
        if (item == null)
        {
            return null;
        }
        T newItem = new T();
        foreach (System.Reflection.PropertyInfo prop in item.GetType().GetProperties())
        {
            if ((prop.PropertyType.IsValueType || prop.PropertyType.Name == "String") && 
                !prop.PropertyType.Name.StartsWith("EntitySet") &&
                !("|"+OmitPropeties+"|").ToLower().Contains("|"+prop.Name.ToLower()+"|"))
            {
                System.Reflection.PropertyInfo prop2 = item.GetType().GetProperty(prop.Name);
                prop2.SetValue(newItem, prop.GetValue(item, null), null);
            }
        }
        return newItem;
    }
}

Then you call it like…

// get the source record
ShippingInfo TempShipping;
TempShipping = (from s in db.ShippingInfos 
          where s.Id == tempShippingId select s).FirstOrDefault();

// shallow copy the object
ShippingInfo NewShipping = Helpers.AnyObject<shippinginfo>.ShallowCopy(TempShipping, 
                                              "Id|ModifiedBy|ModifiedTime");

// override some copied fields
NewShipping.CreatedBy = PageContext.UserId;
NewShipping.CreatedTime = DateTime.Now;

// insert it
db.ShippingInfos.InsertOnSubmit(NewShipping);
db.SubmitChanges();

Returning a Strongly-Type IEnumerable from a SQL Stored Procedure Using LINQ to SQL

.NET, c#, code, LINQ, microsoft, sql

This is surprisingly difficult, but there’s a relatively robust way to do it:

Create your stored procedure
Create a view that mimics the return signature of the stored procedure
In the model (.dbml), import both the stored procedure and view
Set the “Return Type” property of the stored procedure Data Function to the view type
In code, use the .ToList() method of the stored procedure function of the model and cast it to an IEnumerable of the view type, for example:

DBDataContext db = new DBDataContext();
IEnumerable<vwUspEventCalendarResultSet> CalendarResults = db.uspEventCalendar(CustomerId, StartDate, EndDate, DataStartDate, DataEndDate).ToList();

One thing to be careful of is if your stored procedure might return null values in integer key columns you may have to ISNULL them to 0 or some other meaningful value (in the stored procedure) since LINQ to SQL does not pick up a nullable int type from the view signature.

Here are the code bits to back this up…

View:

SELECT     dbo.AllDates.TheDate, dbo.Events.Id, dbo.Events.Name, dbo.Events.Description, dbo.Events.Url, dbo.Events.CustomerId, dbo.Events.TeamId, 
                      dbo.Events.ShowCityId, dbo.Events.EventStartDate, dbo.Events.EventEndDate, dbo.Events.EventMoveInDate, dbo.Events.EventMoveOutDate, 
                      dbo.Events.EventAddressId, dbo.Events.InventoryPullDate, dbo.Events.InventoryShipDate, dbo.Events.InventoryReturnDate, 
                      dbo.Events.InventoryAvailableDate, dbo.Events.EventShippingInfoId, dbo.Events.EventReturnAddressId, dbo.Events.EventAttendance, 
                      dbo.Events.BoothNumber, dbo.Events.BoothSize, dbo.Events.CeilingHeight, dbo.Events.BoothAttendance, dbo.Events.LeadsCaptured, 
                      dbo.Events.CreatedBy, dbo.Events.CreatedTime, dbo.Events.ModifiedBy, dbo.Events.ModifiedTime, dbo.Events.Deleted, dbo.Events.Active, 
                      dbo.Events.Notes
FROM         dbo.Events INNER JOIN
                      dbo.AllDates ON dbo.Events.EventStartDate = dbo.AllDates.TheDate

Stored Procedure:

ALTER PROCEDURE [dbo].[uspEventCalendar] 
	-- Add the parameters for the stored procedure here
	@CustomerId int = 0, 
	@StartDate datetime = null,
	@EndDate datetime = null,
	@DataStartDate datetime = null,
	@DataEndDate datetime = null
AS
BEGIN
	-- default to "this month"
	if @StartDate is null 
		SET @StartDate = cast(cast(DatePart(month,getdate()) as varchar) + '/1/' + cast (DatePart(year,getdate()) as varchar) as datetime)
	if @EndDate is null 
		SET @EndDate = dateadd(day,-1,dateadd(month,1,@StartDate))
	if @DataStartDate is null 
		SET @DataStartDate = @StartDate
	if @DataEndDate is null 
		SET @DataEndDate = @StartDate

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
	--SELECT @StartDate,@EndDate
    -- Insert statements for procedure here
	
	SELECT CalendarDates.TheDate,
					ISNULL(EventCalendarEntries.Id,0) as Id, EventCalendarEntries.Name, EventCalendarEntries.Description, EventCalendarEntries.Url, EventCalendarEntries.CustomerId, 
                      EventCalendarEntries.TeamId, EventCalendarEntries.ShowCityId, EventCalendarEntries.EventStartDate, EventCalendarEntries.EventEndDate, EventCalendarEntries.EventMoveInDate, 
                      EventCalendarEntries.EventMoveOutDate, EventCalendarEntries.EventAddressId, EventCalendarEntries.InventoryPullDate, EventCalendarEntries.InventoryShipDate, 
                      EventCalendarEntries.InventoryReturnDate, EventCalendarEntries.InventoryAvailableDate, EventCalendarEntries.EventShippingInfoId, 
                      EventCalendarEntries.EventReturnAddressId, EventCalendarEntries.EventAttendance, EventCalendarEntries.BoothNumber, EventCalendarEntries.BoothSize, 
                      EventCalendarEntries.CeilingHeight, EventCalendarEntries.BoothAttendance, EventCalendarEntries.LeadsCaptured, EventCalendarEntries.CreatedBy, 
                      EventCalendarEntries.CreatedTime, EventCalendarEntries.ModifiedBy, EventCalendarEntries.ModifiedTime, EventCalendarEntries.Deleted, EventCalendarEntries.Active, 
                      EventCalendarEntries.Notes
		 FROM 
		(SELECT thedate from alldates where thedate BETWEEN @StartDate AND @EndDate) as CalendarDates LEFT OUTER JOIN
		(SELECT     EventDates.TheDate, EventsInRange.Id, EventsInRange.Name, EventsInRange.Description, EventsInRange.Url, EventsInRange.CustomerId, 
                      EventsInRange.TeamId, EventsInRange.ShowCityId, EventsInRange.EventStartDate, EventsInRange.EventEndDate, EventsInRange.EventMoveInDate, 
                      EventsInRange.EventMoveOutDate, EventsInRange.EventAddressId, EventsInRange.InventoryPullDate, EventsInRange.InventoryShipDate, 
                      EventsInRange.InventoryReturnDate, EventsInRange.InventoryAvailableDate, EventsInRange.EventShippingInfoId, 
                      EventsInRange.EventReturnAddressId, EventsInRange.EventAttendance, EventsInRange.BoothNumber, EventsInRange.BoothSize, 
                      EventsInRange.CeilingHeight, EventsInRange.BoothAttendance, EventsInRange.LeadsCaptured, EventsInRange.CreatedBy, 
                      EventsInRange.CreatedTime, EventsInRange.ModifiedBy, EventsInRange.ModifiedTime, EventsInRange.Deleted, EventsInRange.Active, 
                      EventsInRange.Notes
FROM         (SELECT     Id, Name, Description, Url, CustomerId, TeamId, ShowCityId, EventStartDate, EventEndDate, EventMoveInDate, EventMoveOutDate, 
                                              EventAddressId, InventoryPullDate, InventoryShipDate, InventoryReturnDate, InventoryAvailableDate, EventShippingInfoId, 
                                              EventReturnAddressId, EventAttendance, BoothNumber, BoothSize, CeilingHeight, BoothAttendance, LeadsCaptured, CreatedBy, 
                                              CreatedTime, ModifiedBy, ModifiedTime, Deleted, Active, Notes
                       FROM          Events
                       WHERE      (EventStartDate < = @DataEndDate) AND (EventEndDate >= @DataStartDate)) AS EventsInRange CROSS JOIN
                          (SELECT     TheDate
                            FROM          AllDates
                            WHERE      (TheDate BETWEEN @StartDate AND @EndDate)) AS EventDates
WHERE     (EventDates.TheDate BETWEEN EventsInRange.EventStartDate AND EventsInRange.EventEndDate)) EventCalendarEntries ON EventCalendarEntries.thedate = CalendarDates.thedate
END

Cast an ArrayList as IEnumerable (and Even IQueryable)

.NET, c#, code, LINQ, microsoft,

Again, not really sure why this works. Based on the original from here.

ArrayList students = GetStudents();
var query =
  from student in students.Cast()
  where student.Score > 80
  select new { student.ID, student.Name };

This is what I ended up with:

IQueryable dcs = 
(IQueryable)getChildControlsByType(ContactDetailsView, "System.Web.DynamicData.DynamicControl").Cast().AsQueryable();

Where the method getChildControlsByType is returning a generic ArrayList (but always of some kind of control). I guess the “Cast,” being an extension method, returns a typed IEnumerable, which can be turned into an IQueryable. Without the Cast, this code throws an exception.

LINQ Left Join with Where on Both Tables

.NET, c#, code, LINQ

I struggled with this one for a bit, but here’s how you do a LINQ “LEFT JOIN” equivalent while applying WHERE to both tables and maintaining an “all” relationship on the first table:

var TeamAllocations = from team in db.Teams
where team.CustomerId == PageContext.CustomerId
join a in
(from x in db.TeamAssetAllocations where x.AssetId == _AssetId select x) on team.Id equals a.TeamId into ta
from a in ta.DefaultIfEmpty()
select new { team.Id, team.Name, a.QuantityAllowed };

Personally, I don’t really get it, especially the “from a in ta.DefaultIfEmpty()” part, but whatever.