Handling logical delete with Entity Framework 4

Many systems use logical delete in DB, instead of physical delete. For example, when the end user want to delete some records from the DB (not directly, of course), the Data access layer should only set the record’s Deleted column to true, and not use the SQL Delete statement. This behavior is very common used, and very convenient for restoring the data later in case of mistakes. But, of course, it has it drawbacks. In this article, I want to focus on two of them, specifically when using Entity Framework 4:

1. Filtering every query with ‘Deleted = false’ can be very exhausting job. When using Include(), filtering on the second and below levels can force us sometime create more than one query.

2. As I said, sometimes only part of the tables use the logical delete, and others keeps with the physical. We would like to have a generic solution for that, without making changes all the time. For example, if tomorrow we will want to add a logical delete for some table, of course we’re not going to change each query this table appears in.

So, what we got?

I’ve created some simple model, describing Schools DB. As you can see in the diagram, the School & Class entity has logical delete support (by ‘Deleted’ column), and the Student entity needs to be physically deleted when calling delete:

image

First, we will add condition  to the School & Class entities, forcing the model only bring us the undeleted rows (our assumption is, that we really won’t need any deleted rows in our code), like this:

image

We should also delete the ‘Deleted’ column from the model, in order to avoid exceptions. Meaning of this is, that from now on, we will not see the ‘Deleted’ column in our code. Only undeleted rows will be retrieved from DB, and no developer can set (or filter by) this column. So, how we goanna delete exists column?

For this purpose we need to add some code. lets create a partial class for our context, and override the SaveChanges() method. This method is been called each time the model saving changes, so we can catch all deleted items here, and decide whether to actually delete it or not:

var deleted = this.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted);

Next, we will look for each item if he has ‘Deleted’ column in the DB, and execute direct  Update statement. For doing that, we will need to investigate the SSDL (Store model). Remember that Entity framework works with 3 models:

1. CSDL – the conceptual model, describing what you’re going to see in your code.

2. SSDL – the store model, describing your DB.

3. C-S mapping – the mapping model, describing the connections between the two above.

Because we already delete the ‘Deleted’ column from the CSDL, we should ask the SSDL for this column, like this:

var storeItem =  this.MetadataWorkspace.GetItems<EntityType>(DataSpace.SSpace).Where(et => et.Name == deletedItem.EntitySet.Name).FirstOrDefault();

if (storeItem != null)
{
//find the delete property
var deleteProperty = storeItem.Properties.Where(ep => ep.Name == "Deleted").FirstOrDefault();

For simplicity, I supposed that EntitySet.Name and the store name should be equal. Of course, if it’s not, just explore the mapping model to find the correct name. Notice that there is some trick when working with SSDL metadata. Entity framework first load the CSDL, and the SSDL will not be loaded till he needs to create a query for the first time. In order to force the SSDL to load, we will make a fake call to the ToTraceString() method before we will investigate the SSDL. This should be called only one per process:

private void EnforceSsdl()
{
(this.Classes as ObjectQuery).ToTraceString();
}

Next, we will get the entity key name and value, in order to execute direct update on the DB. This code is only for the simple case where you have only 1 key. For complex cases, simply change this:

const string deleteSql = "Update {0} Set Deleted=1 Where {1}={2}";

var keyName = storeItem.KeyMembers.First().Name;
var keyValue = deletedItem.EntityKey.EntityKeyValues.First().Value;
var sql = string.Format(deleteSql, storeItem.Name, keyName, keyValue);
this.ExecuteStoreCommand(sql);

Finally, don’t forget to change this item state back to unchanged, to ensure that EF will not delete it as part of the transaction. Close the method with returning the base.SaveChanges() and it’s done. you can find the Full source code link below.

Source Code Here

Advertisements
Categories: C#, Entity Framework Tags: , ,

Entity Framework 4–Load metadata exception

What could be more simple?

I’ve created Console project (same for Test projects) to check my DAL created with EntityFramework 4. Everything looked Located right, everything compile, and yet, I keep get the following message:

“The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.”

So I open Google to find out what it is. I found some great posts, talk about a case like mine – when the edmx model is not the executed one. Some said copy the config file, some said edit the connection string and tell to EF where to find the Meatadata,
But somehow – still nothing worked for me.

After a short investigation, I discovered that when the model is called the first time, all the metadata uploaded to memory, according to the metadata section in the connection string:

connectionString="metadata=res://*/DataModel.csdl|
res://*/DataModel.ssdl|
res://*/DataModel.msl;
provider=System.Data.SqlClient;
provider connection string=&quot;
data source=.\sql2008;
initial catalog=AdventureWorks;
integrated security=True;
multipleactiveresultsets=True;
App=EntityFramework&quot;"

My solution actually contains three projects: one containing the model, second containing the Entities created by using the SelfTracking template, and the third – the console project I ran.
In fact, only because I called only classes from the second and third projects –the DLL that contains the metadata files as a resource not yet been loaded to memory, so the metadata didn’t found.
When ObjectContext constructor called, he actually scans all loaded files for metadata resource, and if he cant find it, he throws the exception above.

All you need do is force the loading of the metadata DLL, like calling Assembly.LoadFile () or something similar, or by simply declare a class from that DLL, and it will load automatically.

Categories: C#, Entity Framework Tags: , ,

Creating Multi-key Dictionary object

Dictionary object has many different uses, and since .Net 2.0 the generic support makes it more common. The biggest limitation of the classic Dictionary<TKey, TValue> is that it has only one key. If you need more than one key, you need to look how to customize it.

Maybe a List<T>?

My first thought was about generic List<T>. Suppose I want to use only string values, I can create the Dictionary<List<String>,String> so I can manage the keys in a list like this:

var listDictionary = new Dictionary<List<string>, string>();

But this code has one big limitation. Take a look:

listDictionary.Add(new List<string>
{
"Key1","Key2"
}, "val");
listDictionary.Add(new List<string>
{
"Key1","Key2"
}, "val");
Console.WriteLine("Exception should be thrown already...");

When the Add() method is looking for matching elements in the keys collection to prevent duplicate key, he can’t find that two keys are equal. It’s the same when you trying to call Equal() on a list:

var l1 = new List<string> { "A" };
var l2 = new List<string> { "A" };
Console.WriteLine(l1.Equals(l2));
//Output: False

This is why we should use in such cases the method SequenceEqual:

var l1 = new List<string> { "A" };
var l2 = new List<string> { "A" };
Console.WriteLine(l1.SequenceEqual(l2));
//Output: True

I can always create an extension method and “override” the Add() and ContainsKey() methods, but of course this is not my first choice.

Custom Class

Another option is creating custom class with property for each key. Same as List<T>, it won’t work because it’s a reference type, and by adding new instance of this class we’re taking a risk to add existing key to the collection (and hence,  not getting the correct value when we want it back).

Tuple

In .Net 4.0 Microsoft introduced the Tuple class, which simply  allow creating object with variety of typed fields. The main goal of that object (at least, for our needs) that it’s act like an immutable type – you cannot changed it after it’s first created. For that reason the  default Equals() method will return true for two different instances of the Tuple class with the same values, not like the List<T> type. Creating the “Tupled” Dictionary for the sample above will look like this:

var tupleDictionary = new Dictionary<Tuple<string, string>, string>();

tupleDictionary.Add(new Tuple<string, string>("key1", "key2"), "val");
//When adding this item exception will be thrown as expected
tupleDictionary.Add(new Tuple<string, string>("key1", "key2"), "val");

Now, you can safely call the Add() and ContainsKey() method.

Categories: C# Tags: , , ,

Installing Windows Server 2008 R2 as a guest OS on VMware server

last week I wanted to install a VM on a laptop, in order to install Windows Server 2008 R2 x64 as a guest operating system. I downloaded VMware server, which is free now, and insert the OS disk, but I got black screen telling me my hardware is not compatible for 64 bit operating system.

It was very strange, because my host OS is 64 bit already. I googled on it a little bit, and I found the Processor Check for 64-bit compatibility tool from VMware. after running the tool I found out that my processor is good enough for running 64 bit VM’s, and I must look for the problem in another place.

my next step was raising an issue in the VMware community forum, and finally I got the answer from there: Enable the VT-x setting in the BIOS menu. I put the OS disk drive again, and now it works perfectly!

Recover demaged MS-Word file

Recently I was asked to try to repair Word file, when you open it you receive the the following message:

The name in the end tag of the element must match the element type in the start tag”

image

The file was loaded with mathematical formulas, and attempt to restore it or put in earlier versions failed. So how do you start?

Starting from a version 2007, Microsoft has adopted an agreed format for Word documents, Excel and Power Point, called Open XML Format. In fact, every Word file built from a collection of xml files, and all together contracted by zip, plus a “docx” extension. Suppose we have a file named MyDoc.docx, by changing the name to MyDoc.docx.zip, we can Extract with simple zip software, and receive a folder with all the xml files. To bring it back to a word file, simply compress back, and download the zip file name extension.

Step 1: Extracting to XML Files

First we’ll copy the corrupted file, in order not to hurt him more than he has been hurt. For example, let’s call him ErrorFile.docx. We will add the .zip extension. to the file name, and double clicking on it will open the actual extracted files. At this point we can see the structure of the xml.

image

Step 2: Finding the damaged line

Pay attention to the error message we got- the last line refers us to word/document.xml, line 2, column 93,496. Unfortunately, Word does not really handle the layout of the xml file, so most of the file is in one line, and that’s why we got this number – 93,496. The Document.xml file, is the file that manage all the structure of the Word document, and is actually the main file of any word document. In order to  work on it properly, we’ll copy it to another library.

To find the specific error in the file, please refer to the exact column that appears in the error description, and find why the file is invalid in this area. In practice, since we are dealing with large files, we might have to scan quite a lot of lines before we can understand where is the mistake. To minimize this time, you can simply open a new xml file on VisualStudio 2010 and paste the file into the design surface, and it will be done automatically. Changing the file lines cause the line number in the error message to be  irrelevant for us, but do not worry – Notice the red dots on the ScrollBar, they indicate the location of the error.image

A quick check by collapsing unnecessary elements resulted the following picture,that explains it all:

image

A sequence of elements that arranged not in the correct order – the opening element for <AlternateContent> is before <oMath>, but also its closing element. The <Choice> element’s location is not so clear.

Step 3: The solution

In order to find the right ratio of those elements for those who unfamiliar with this format, you should look for the rest of the file – how to do it right. You can find many places whereAlternateContent wraps Choice, and all that’s left is to change the location of oMath. 

Step 4: Running the new file

After fixing the file – document.xml, keep it and replace it with the original document.xml file, found under the ErrorFile.docx.zip we opened. No need to compress (and vice versa, compress by zip software usually will not work). Just return  to the parent directory, remove the zip extension, and open the file properly.

Next time, Please, create versions סמיילי.

 

Categories: General Tags: ,

Outlook Suggestions

A great feature was recently added to Gmail, and I think it’s been called “Gmail Suggestions”. This feature remind you to add contacts to your recipients list when sending mails, based on mails from the past.

quick search on Google.com may result the same feature for Outlook, but since I wanted to write some add-in for Outlook 2010 long time ago, it’s sounds like a nice practice.

in this example, I’m not going to spend much time in the Contact search algorithm, performance and some other staff – I only want to show the concept. so, this is how it’s goanna work:

  1. Creating an Object for all recipients by sent mails.
  2. When adding recipients to a new mail, we’ll search for relevant contacts and add them to a window located on the bottom of the new mail screen.

Creating Outlook 2010 Add-in Project

First, I’ll create new Outlook add-in project, by selecting the right project template:

image

As you can see, VS created the ThisAddIn.cs file for you, and except the Auto generated code – we have two event handlers here: one for the startup, and the second for shutdown.

As I said before, in order to complete this feature and make it ready for publish, some points need to be considered. For now, I only want to concentrate in the basic concept: suggest the outlook user some recipients he may forgot.

So first, whenever the outlook client is launched, I’ll look for any sent mail and found the recipients. This operation might be heavy for large mailboxes, so we should consider change this mechanism before production. For now, it should be fine:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
Contacts = new Dictionary<List<string>, int>();

Outlook.NameSpace ns = this.Application.GetNamespace("MAPI");
Outlook.MAPIFolder sentMailsFolder = ns.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail);

foreach (Outlook.MailItem item in sentMailsFolder.Items.OfType<Outlook.MailItem>())
{
var contactList = new List<string>();

foreach (Outlook.Recipient rec in item.Recipients)
{
contactList.Add(rec.Name);
}

contactList = contactList.OrderBy(c => c).ToList();

/**
* Contacts.ContainsKey won't work here beacuse the list has different reference.
* So we must user foreach.
**/
bool found = false;
foreach (var contacts in Contacts.Keys)
{
if (contacts.SequenceEqual(contactList))
{
contactList = contacts;
found = true;
continue;
}
}

if (!found)
Contacts.Add(contactList, 1);
else
Contacts[contactList]++;
}

Creating the Form Region

Inside Outlook mail window, we want to suggest the user some relevant contacts, and allow him to add them to the To line as quickly as possible.

Right click on your project –> Create New Item – > Outlook Form region, wizard will appear:

We want to create a new form region, so in the first screen the first option will fit our needs. click next in order to get the Type screen. In the type screen we have some options:

  • Separate – this option creates different page to the form, so you cannot see the mail body and the form region at the same time.
  • Adjoining – Select this option, in order the get the form region as part of the mail body screen. we’ll choose that.
  • Replacement – like Separate, this will create new page, but this option will mark this page as the default page for this item.
  • Replace All – let the form region be the only page for that item.

image

Select the Adjoining option, and click next.

In the next page, give the region name, say Suggestion, and check only the first check-box. we will only want the region to appear when we’re in compose mode, no suggestions needed in reading pane / reading mode. After clicking next. keep the MailMessage item as the only one checked, and click Finish.

Create the Suggestion control

Basically outlook region form uses win forms control, so we’ll use simple Flow Layout control for our suggestion control. Go to the toolbox, and add FlowLayout control to the design surface, set the docking to Fill, and add some other design properties if you like to.

when creating the form region, Visual studio automatically created a code behind file with a partial factory class named (in our case) SugestionsFactory. this partial class already contains event handler for the FormRegionInitializing event, so we can make some manipulations like cancel the form region before it’s get into view. For now, no need to put any code over there.

The only thing we need to care of is, updating the suggestion list based on the contacts already appear in the To list. In order to do that, we’ll create a listener to the PropertyChanged event when the form loaded, like this:

private void Suggestions_FormRegionShowing(object sender, System.EventArgs e)
{
this.item = this.OutlookItem as Outlook.MailItem;
item.PropertyChange += new Outlook.ItemEvents_10_PropertyChangeEventHandler(item_PropertyChange);
}

Now, when this listener is called, just need to select the relevant suggestions from the Contacts static property we’ve created before. We decently have more than one option here. my logic was to take all the suggestions that contains all the current recipients, and give them a rank based on how many mails sent to them. then, I choose the best 7, thus not giving the user list with hundreds of items.

After choosing the relevant contacts, add them to the FlowLayout control with a ClickEventHandler. Whenever the user clicks a contact, it should be added to contact list. Notice we called Resolve to force Outlook to resolve this new string as a contact:

void ShowSuggestion(string contact)
{
LinkLabel lb = new LinkLabel();
lb.Text = contact;
lb.Click += new EventHandler(lb_Click);
this.flowLayoutPanel1.Controls.Add(lb);
}

void lb_Click(object sender, EventArgs e)
{
LinkLabel lb = sender as LinkLabel;
item.Recipients.Add(lb.Text);
item.Recipients.ResolveAll();
}

Many things need to be done before using this simple app in real world scenario, like handling contact name changing, and refresh the contact list when new item sent, etc. For now, this is how it looks like:

image

Enjoyסמיילי

Source Code Here

Categories: C#, Outlook Tags: , , , , ,

Hebrew Extension methods

The .NET framework has great support for date globalization, and it’s very easy making conversion between the regular DateTime object and your specific one. Nonetheless, you still need to create your specific CultureInfo and set the specific calendar. Then, you should use the overloaded DateTime.ToString method with the cultureInfo and the format string, in order to get the desired result. If you intend to use one specific culture in your application, it could be very annoying writing this code every time.

A year ago, I had to write some code with an HebrewCalendar support. so I created an ExtendedDateTime class which handles all the date calculation for this calendar (Basically, all the cultured date classes are state-less, unlike the regular DateTime). But, after i saw Scott Mitchel great article about 5 Helpful DateTime Extension Methods, I thought it will be better providing extension methods for that propose. I’ll demonstrate here some, and the full source code will be available for download soon.

For all the DateTimeExtensions I’ll use the same culture object, like this:

static HebrewCalendar hebrewCalendar = new HebrewCalendar();
static CultureInfo cultureInfo = CultureInfo.CreateSpecificCulture("he-IL");

This 2 lines are not enough, and i also need to set the DateTimeFormat of the culture to the specific calendar I’ve just created. Because extension methods are part of a static class, I’ll use static constructor for doing this:

static DateTimeExtensions()
{
      cultureInfo.DateTimeFormat.Calendar = hebrewCalendar;
}

After creating those, writing the methods is simple, by using the built-in functions of the HebrewCalendar class. here are some examples:

public static string ToHebrewDateString(this System.DateTime date)
{
     return date.ToString("dd בMMMM, yyyy", cultureInfo);
}
public static bool IsLeapHebrewYear(this DateTime date)
{
     return hebrewCalendar.IsLeapYear(hebrewCalendar.GetYear(date));
}

Now, handling Hebrew dates is simple like handling basic dates:

DateTime.Now.ToHebrewDateString();

 

I’ve also added some extension methods for Hebrew gematria (Integer and String objects), full source code will be available soon.

Categories: C# Tags: , ,
%d bloggers like this: