gistfile1.cs

public static SendsDownloads Get_AssetUsage( string assetId ) {
  using( DAMReaderDataContext db = new DAMReaderDataContext() ) {

		var downloadEntities = db.DownloadAssets
			.Where( da => da.AssetId == assetId )
			.Select( da => da.Download )
			.Where( d => d.DownloadStatus == "SUCCESSFUL" && d.SendReceiverId == null );

		var sendReceiverEntities = db.SendAssets
									 .Where( sa => sa.AssetId == assetId )
									 .Join( db.SendReceivers, sa => sa.SendId, sr => sr.SendId, ( sa, sr ) => new {
										 Sender = sa.Send.User,
										 DownloadsStarted = sr.Downloads_Started,
										 ReciverUserId = sr.ReceiverUserId,
										 ReceiverEmail = sr.Email
									 } )
									 .Where( download => download.DownloadsStarted > 0 && download.ReciverUserId == null )
									 .Select( download => new SendDownloadUser {
										 SentByName = download.Sender.Firstname + " " + download.Sender.Lastname,
										 SentByEmail = download.Sender.Email,
										 Email = download.ReceiverEmail
									 } );

		var sendNonDownloadedReceiverEntities = db.SendAssets
												  .Where( sa => sa.AssetId == assetId )
												  .Join( db.SendReceivers, sa => sa.SendId, sr => sr.SendId, ( sa, sr ) => new {
													  Sender = sa.Send.User,
													  DownloadsStarted = sr.Downloads_Started,
													  ReciverUserId = sr.ReceiverUserId,
													  ReceiverEmail = sr.Email
												  } )
												  .Where( download => download.DownloadsStarted == 0 && download.ReciverUserId == null )
												  .Select( download => new SendDownloadUser {
													  SentByName = download.Sender.Firstname + " " + download.Sender.Lastname,
													  SentByEmail = download.Sender.Email,
													  Email = download.ReceiverEmail
												  } );

		var result = new SendsDownloads {
			Downloads = downloadEntities.Select( de => new BatchDownloadUser {
				Name = de.User1.Firstname + " " + de.User1.Lastname,
				BatchType = de.BatchType,
				Email = de.User1.Email
			} ).ToList(),
			SendDownloads = sendReceiverEntities.Select( sre => new SendDownloadUser {
				SentByName = sre.SentByName,
				SentByEmail = sre.SentByEmail,
				Email = sre.Email
			} ).ToList(),
			SendNonDownloads = sendNonDownloadedReceiverEntities.Select( sre => new SendDownloadUser {
				SentByName = sre.SentByName,
				SentByEmail = sre.SentByEmail,
				Email = sre.Email
			} ).ToList()
		};

		return result;
	}
}