Monday, 10 September 2012

Selecting Hierarchical data with WCF Data Services

In this post I’ll describe how you can use hierarchical data with WCF Data Services as it’s not immediately obvious how to do this, and there’s also an issue (which I believe to be a bug) that needs to be addressed in order for it to work.

In this example I have a simple table shown below, with a hierarchical link between employees. The keys are integers, defined as IDENTITY columns in SQL server. All the code for this example is attached to this post if you should wish to look into it further.

image

I’ve also created a stored procedure that selects people in a hierarchical manner using a CTE. This is as follows…

-- Create an SP that selects a hierarchical set of data
CREATE PROCEDURE SelectPersonHierarchy(@personId int) AS
SET NOCOUNT ON
;
WITH PersonHierarchy AS
(
SELECT PersonId, Name, ManagerId
FROM Person
WHERE PersonId = @personId
UNION ALL
SELECT p.PersonId, p.Name, p.ManagerId
FROM Person p
INNER JOIN PersonHierarchy ph
ON p.ManagerId = ph.PersonId
)
SELECT PersonId, Name, ManagerId
FROM PersonHierarchy
GO

So far so good. Next I created an EF model that includes the table above and the stored procedure too. Then I created a simple WCF data service as shown below – the extra method is used to expose the hierarchical stored procedure, so that clients can call it directly.


public class PersonService : DataService<PersonEntities>
{
public static void InitializeService(DataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);

// And set the protocol version used
config.DataServiceBehavior.MaxProtocolVersion =
DataServiceProtocolVersion
.V2;
}

[WebGet]
public IQueryable<Person> GetFullPerson(int personId)
{
return this.CurrentDataSource.
SelectPersonHierarchy(personId).AsQueryable();
}
}

Next I created the client for this service using Add Service Reference. Then I created a method that would call the GetFullPerson method on the server, the outline of which is shown below…


public Person GetFullPerson(int personId, bool makeItWork)
{
Person root = null;

this.CreateQuery<Person>("GetFullPerson")
.AddQueryOption("personId", personId).ToList().ForEach(
t =>
{
if (null == t.ManagerId)
root = t;
else
{
Person parent = this.FindManager(root, t.ManagerId.Value);
parent.Subordinates.Add(t);
}
});

return root;
}

This method converts the data returned from the WCF operation to a list, iterates through that list and builds the client Person hierarchy (using a small helper function FindManager which, given a tree of objects, looks for the manager of the current person).


So, with all that in place we can write some unit tests to test the service. In my tests I’ve just used Boss and Employee as my two people. First off we’ll insert a Boss and an Employee & link them together…


Uri geller = new Uri("http://localhost:11341/PersonService.svc");
ServerService.PersonEntities context =
new ServerService.PersonEntities(geller);

// Insert hierarchical data to test with
var boss = new ServerService.Person { Name = "Boss" };
context.AddToPeople(boss);

var employee = new ServerService.Person { Name = "Employee", Manager = boss };
context.AddToPeople(employee);

// Important to add this - otherwise EF won't record the parent/child
// link in the database
context.AddLink(boss, "Subordinates", employee);

// Now save this to the server
context.SaveChanges();

Apologies for the awful pun in the Uri variable, I just can’t help myself. Here I create a boss, add an employee and then save the changes to the database. The important thing to mention is that you must also call context.AddLink() to ensure that there is a link between the parent and child objects. Here “Subordinates” is the name of the EF navigation property that is mapped from the parent of the relationship to the child. After saving these changes in the database you’ll see the following in the DB (Id’s will differ)…


image


OK, so far so good, we can create and save data to the server.


The Problem


The next thing I wanted to do was to verify that I could reload a person (in a hierarchical manner). So, my unit test was basically as follows…



  • Create a Boss and an Employee

  • Save these to the database

  • Reload these and test that all data was correct, including the link between employee and boss. This is where it all started to go wrong.

The reload code wasn’t working properly, and I tracked it down to the ManagerId field (after a fair amount of time). First I used Fiddler to log what was coming down from the server. My request looked OK…


image


And the data coming back from the server also looked OK…


image


However in my code that was looping through the data that came from the server the ManagerId was always null, for each and every row returned from the server.


In another test however it was fine – what gives?


The Solution


My two unit tests were identical – well, nearly. One verified what it got inline, another called a method to do that verification and the crucial difference was that the one that worked used a new service context. So, if you create a hierarchy of Boss & Employee on the client, save changes and reload (using the same client proxy), the hierarchical link is broken. If however you create a new client proxy and call the server everything works as expected.


My solution to this was to include the following in the method that calls the server…


public Person GetFullPerson(int personId)
{
Person root = null;

var currentMergeOption = this.MergeOption;
this.MergeOption = MergeOption.OverwriteChanges;

this.CreateQuery<Person>("GetFullPerson")
.AddQueryOption("personId", personId).ToList().ForEach(
t =>
{
if (null == t.ManagerId)
root = t;
else
{
Person parent = this.FindManager(root, t.ManagerId.Value);
parent.Subordinates.Add(t);
}
});

this.MergeOption = currentMergeOption;

return root;
}

This ensures that the data read from the server overwrites the data on the client, and in this instance that’s enough for the ManagerId to be set correctly rather than being Null.


Example Code


The following is a link to a .zip file that contains the same code. You’ll find a database script in the EFHierarchyProblem project called SchemaAndLogic.sql. Run this against your database.


You should also update the database connection strings in each project to reference your database. There are 3 projects in the solution, a class library that contains the EF model and the service, a Web project that exposes that service, and a Test project that runs the unit tests. When you run the tests you should see the following in the test results window…


image


The Test_ServerService_WillFail should work but doesn’t, as the ManagerId is overwritten when reloading the data. The _WillWorkSameContext test uses the merge option to overwrite local data to ensure that the ManagerId isn’t overwritten, and the _WillWorkNewContext test inserts data but then requests it from a new client proxy which will always work.


The .zip can be downloaded from here.

83 comments:

ashtonmullens said...

In the case of drastically changing functions, it should be borne in mind that the program may not notice a sharp change of function. Suppose that when a member is of degree s, and let a real integral function remain the same. Where g (x) is practically different from 0 only on a small segment in the Maths assignment and college essay for sale sample.

Richard Majece said...

I like to look for different useful info online. On https://justdomyhomework.com/blog/funny-homework-answers-awards I found really funny info about homework and now I feel even more motivated.

Michael Jones said...

Programming Assignment help
I understand that it becomes difficult to take out time for assignments and other academic works, due to the busy schedule. But with Programming Assignment Help one can solve this issue as well. Make sure that you take the help of a genuine writing service as these services can help you understand the basics from the first level.

Domenic Tylor said...

Today race is very high for a better career and job. We need to do very much hard work to get a good job and a successful career. However, we are felling some absence of knowledge due to weak basics in our academics. Therefore, do more focus on your basics Australiaassignmenthelp.com provides your homework help.

james cook said...

It is nice to read such high-quality content. It is a good article that discusses the topic at hand quite well. I am looking forward to read more articles from your site. Keep up the good work.
assignment help

Angel Claudia said...

Our Pay for pre written essays for sale services and legitimate essay writing company makes your college life bearable and fulfilling by enabling you to pass your exams and also in other research and essay writing tasks.

Assignment Help said...

I appreciate the effort you made to share the knowledge. This is really great stuff for sharing. We also provide an assignment to help Australia students.
Data Structure Assignment Help

KeiraDoltan said...


Nice Blog! As you may know, it is generally an Assignment assigned to students towards the end of their semester. Naturally, students find it difficult to focus on the task while preparing for the finals simultaneously. If you are also facing such a problem,
Make My Assignment are here to help.

Puremelda said...

All our writers understand Already Written Essays standards, and they are always ready to work for you. Students can Cheap Custom Term Papers in any field and on any level of education.

Devi.Angularjs said...

IEEE Project Domain management in software engineering is distinct from traditional project deveopment in that software projects have a unique lifecycle process that requires multiple rounds of testing, updating, and faculty feedback. A IEEE Domain project Final Year Projects for CSE system development life cycle is essentially a phased project model that defines the organizational constraints of a large-scale systems project. The methods used in a IEEE DOmain Project systems development life cycle strategy Project Centers in Chennai For CSE provide clearly defined phases of work to plan, design, test, deploy, and maintain information systems.


This is enough for me. I want to write software that anyone can use, and virtually everyone who has an internet connected device with a screen can use apps written in JavaScript. JavaScript Training in Chennai JavaScript was used for little more than mouse hover animations and little calculations to make static websites feel more interactive. Let’s assume 90% of all websites using JavaScript use it in a trivial way. That still leaves 150 million substantial JavaScript Training in Chennai JavaScript applications.

Puremelda said...

As much as an Affordable Term Papers is a summary of other works that should not be an excuse to many Pre Written Research Paper with plagiarized work. They ensure that the arguments are presented in a discussion and reflective manner to maintain the aspect of originality as well as show the audience that they understand what they are presenting on the Buy Pre Written Essays.

meldaresearchusa said...

Among the most popular Legitimate Essay Writing Serviceprovided to students is Medical Term Papersintended to meet your academic needs,as well as Legitimate Essay Writing Services.

Florahmelda said...

Do you require Genuine Nursing Research Papers from a trusted Nursing Research Papers Writing Service and Reliable Nursing Assignment Help firm which guarantees high-quality Papers for the client?

noor said...


نقل عفش من الدمام الى الرياض نقل عفش من الدمام الى الرياض
ارخص نقل عفش بمكة ارخص نقل عفش بمكة
نقل عفش من جدة الى الاردن نقل عفش من جدة الى الاردن

for ict 99 said...

Great Article. Thank you for sharing! Really an awesome post for every one.

IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

JavaScript Training in Chennai

JavaScript Training in Chennai

Teresa Halminton said...

If you are interested in your favorite actors' birthday, please take a look at celebrity birthdays database.

Scott Stayris said...

Quickbooks is the accounting software that helps to manage business-related transactions. This tool has many premium features to grow your business. Quickbooks File Restore

diyaroy.com said...

Hi i'm Nitu Roy a gorgeous Guwahati, Guwahati Escorts who is prepared to offer
you top quality of fun and unstoppable service with none restriction. i'm a
well-educated and therefore the best immense love provider where I also can
show you each sexual drive which provides you the simplest love feelings
and once you probably did the simplest sensual love with me, you usually
await subsequent meet.
Call Girl
Call Girl
Call Girl
Call Girl

Lisa Jones said...

Do you have any video of that? I’d love to find out some additional information... 먹튀

GOGOLIVE said...

Looking at this article, I miss the time when I didn't wear a mask. 온라인바둑이 Hopefully this corona will end soon. My blog is a blog that mainly posts pictures of daily life before Corona and landscapes at that time. If you want to remember that time again, please visit us.

Qasim Khan said...

Most budget gaming keyboards in this guide are under $50, and will be a stalwart companion to your gaming PC, but will be pretty barebones. As such, certain features will be omitted to keep the price...

Kumar Singh said...

Does closes our rundown of best android games under 1mb that chips away at pretty much every cell phone with no problem.

Unknown said...

FDM printing is one of the most sought-after 3D printing techniques in the world.
is a technology that works both horizontally and vertically, where an extrusion nozzle moves over a build platform. The process involves the use of thermoplastic material that reaches melting point and is then forced out, to create a 3D object layer by layer. As the design takes shape, it is clear to the see each layer as a horizontal cross section. Following the completion of one layer, the nozzle of the printer is lowered in order for the next layer of plastic to be added to the design. Once the object has been created, the materials that are used to support the object can then be removed.

Unknown said...

Hello friends, in today’s article we will see what is "MOSFET", what are its types, and what are its uses. The full name of MOSFET is Metal Oxide Semiconductor Field Effect Transistor. It is a semiconductor device used for the amplification of electronic signals in electronic devices for the purpose of a switch in electronic equipment.

MOSFET is a core or integrated circuit, where it is designed and forged into a single chip because the device is available in a very small size. The discovery of MOSFET has seen new changes in the field of electronic switching

Crown999 said...

I am very impressed with your writingsex I couldn't think of this, but it's amazing! I wrote several posts similar to this one, but please come and see!

Krunal said...

Shoring is a brief design used to forestall the breakdown of the fundamental under-development structure. It is for the most part needed to balance out the development pit dividers, keep water from infiltrating the pit, and secure adjoining structures.

Floating slabs are substantial slabs that are laying over the ground, with no sort of mooring, as though it essentially sits on it and buoys. Floating section, as the name advises it's anything but a plate that is just laid over water, with no sort of association between them. The principle utilization of floating slabs is to use as a base establishment for sheds, fabricating workshops, home extra room, or carports. It is affordable when utilized in regions any place the requirement for standard establishments isn't needed. The development subtleties are clarified exhaustively in the accompanying areas. At the point when we expect a structure with floating plan, we should picturize as a structure with a standard establishment of strip balance, that is developed a few feet beneath the ground.

mechanicaljungle said...

The name it self address that it will have two sliding sets and two turning sets in the twofold slider crank chain.

As you can see the above picture which is a model for the Double slider crank chain. In that, We have four components (two sliding components, one switch, opened one base).

The two sliding components joined by the switch component. This liver will make two turning sets with the two sliding components. The opened base which will make the two sliding sets with the sliding components.

The least complex sort of Centrifugal Governor is the Watt governor. In the past article, we have talked about the Centrifugal governer exhaustively. In this article, we will talk about the Watt Governor in more detail.

The fundamental working standard behind Watt governor is the adjusting of the radial power. So these go under the Centrifugal Governors.

Bhushan said...

What Is Shoring? shoring types is a temporary structure used to prevent the collapse of the main under-construction structure. It is generally required to stabilize the construction pit walls, prevent water.

monolithic slab construction is a procedure where utilizing a homogeneous mixture, a structure is monolithically constructed. It is a structure constructed from a sole material, assembled as well.

What Is 53 grade cement? The Grade assigned to cement indicates the rolling resistance of cement. The strength gained by concrete usually compressive strength is measured after 28 days after.


What Is a Pitched Roof? A pitched roof is a roof that has slopes downwards, typically in two halves at an angle from a central ridge, but sometimes in one part, from one edge to another.

bamgosoocom said...

Hi there mates, how is all, and what you would like to say concerning this
article, in my view its truly awesome in favor of me.

Look at my web-site 오피

Crown999 said...

Your writing is perfect and complete. Kèo hay However, I think it will be more wonderful if your post includes additional topics that I am thinking of. I have a lot of posts on my site similar to your topic. Would you like to visit once?

VISIT ME said...

really like reading through a post that can make people think. Also, many thanks for permitting me to comment! 메이저사이트

VISIT ME said...

I want to to thank you for this good read!! I definitely enjoyed every little bit of it. I’ve got you book-marked to look at new stuff you post. 경마사이트

VISIT ME said...

It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. 바카라사이트

VISIT ME said...

Everything is very open with a clear clarification of the issues. It was really informative. Your site is extremely helpful. Thanks for sharing! 사설토토

opbestcom7 said...

Reading your blog is way better than snuggles. It is very informative and I bet you are very knowledgeable in this area.
I lived out in Alaska so I know what you are talking about here. {My girlfriend introduced me to your posts.|I must say, I really like what you've done to your website.
Your points are really well made and thought up.|Hope you get the issue resolved soon.|I'm probably going to purchase a bean bag chair next week.|Might there be a part two at some point?|I really feel your site needs a lot more followers 대구오피

John said...

دانلود آهنگ جدید

roulettesitetop said...

You delivered such an impressive piece to read, giving every subject enlightenment for us to gain information. Thanks for sharing such information with us due to which my several concepts have been cleared 룰렛

roulettesitetop said...

Fabulous post, you have denoted out some fantastic points, I likewise think this s a very wonderful website. 카지노사이트

casinositeguide.com said...

I would recommend your website to everyone. You have a very good gloss. Write more high-quality articles. I support you. 카지노사이트

baccaratsite.info said...

I think the admin of this site is in fact working hard.Thanks for ones marvelous posting! 바카라사이트인포

casinosite.one said...

This article is very detailed And i am happy reading this!! cheers 카지노사이트

casinosite.zone said...

You have made some good points there. 온라인카지노

casinosite777.info said...

It is very well written, and your points are well-expressed. I request you warmly, please, don’t ever stop writing.
바카라사이트

baccaratsite.top said...

We are really grateful for your blog post for giving a lot of information
카지노사이트

sportstoto.zone said...

Its an amazing website, I really enjoy reading your articles.
토토

baccaratsite.biz said...

All your hard work is much appreciated. This content data gives truly quality and unique information. I’m definitely going to look into it. Really very beneficial tips are provided here and, Thank you so much. Keep up the good works.
온라인카지노

sdgssss said...

Hello! I know this is kinda off topic but I'd figured I'd ask.
Would you be interested in trading links or maybe guest writing a
blog post or vice-versa? My website goes over a lot of the same subjects as yours And
I think we could greatly benefit from each other.
If you are interested feel free to send me an email.
I look forward to hearing from you! Great blog by the
way! 카지노사이트

Kumar Singh said...

win iphones for free at absolutely cost by following some simple surveys

Crown999 said...

When I read your article on this topic, the first thought seems profound and difficult. There is also a bulletin board for discussion of articles and photos similar to this topic on my site, but I would like to visit once when I have time to discuss this topic. sòng bạc


NOMO said...

I’m very pleased to discover this site. I want to to thank you for ones time for this particularly wonderful read!! I definitely savored every part of it and i also have you saved as a favorite to see new information on your blog. 사설토토사이트


Unknown said...

After looking into a number of the articles on your website, I really like your technique of blogging.
I book marked it to my bookmark website list and will be checking back soon. Please visit my web site as well and let me know your opinion.


https://www.totositehot.com

NOMO said...

Youre so right. Im there with you. Your weblog is definitely worth a read if anyone comes throughout it. Im lucky I did because now Ive received a whole new view of this. 샌즈카지노


오피월드 said...

This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect.
인터넷경마

magosucowep

NT said...

I was looking for another article by chance and found your article슬롯사이트 I am writing on this topic, so I think it will help a lot. I leave my blog address below. Please visit once.


NT said...

When I read an article on this topic, 카지노사이트검증 the first thought was profound and difficult, and I wondered if others could understand.. My site has a discussion board for articles and photos similar to this topic. Could you please visit me when you have time to discuss this topic?


Unknown said...

I no uncertainty esteeming each and every bit of it. It is an amazing site and superior to anything normal give. I need to grateful. Marvelous work! Every one of you complete an unfathomable blog, and have some extraordinary substance. Keep doing stunning 메이저사이트순위

Crown999 said...

Your ideas inspired me very much. roulette It's amazing. I want to learn your writing skills. In fact, I also have a website. If you are okay, please visit once and leave your opinion. Thank you.


Mập mập said...

Hello, I'm happy to see some great articles on your site. Would you like to come to my site later? My site also has posts, comments and communities similar to yours. Please visit and take a look 토토사이트

unknown said...

Thanks for such a fantastic blog. Where else could anyone get that kind of info written in such a perfect way? I have a presentation that I am presently writhing on, and I have been on the look out for such great information. 먹튀검증사이트

NOMO said...

Youre so right. Im there with you. Your weblog is definitely worth a read if anyone comes throughout it. Im lucky I did because now Ive received a whole new view of this. 메이저사이트추천


Unknown said...

HOW TO PLAY ROULETTE - All You Need to Know About 샌즈카지노 우리카지노 온라인카지노 Roulette

unknown said...

I accidentally searched and visited your site. I still saw several posts during my visit, but the text was neat and readable. I will quote this post and post it on my blog. Would you like to visit my blog later? 토토사이트순위

Unknown said...

Quel poste fantastique. C'est plein d'informations précieuses. C'est probablement le meilleur.
Guide le plus concis étape par étape. Je n'ai jamais vu un tel post.
Merci pour ce blog si agréable et si instructif.


https://www.safetotosite.pro

unknown said...

While looking for articles on these topics, I came across this article on the site here. As I read your article, I felt like an expert in this field. I have several articles on these topics posted on my site. Could you please visit my homepage? 토토사이트모음

NOMO said...

I finally found what I was looking for! I'm so happy. 우리카지노


louisarthur said...
This comment has been removed by the author.
Unknown said...

Hola, estoy aquí por primera vez. Me encontré con este tablero y encontré
Realmente útil y me ayudó mucho. Espero dar algo a cambio
y ayuda a otros como tú me ayudaste.


https://www.safecasinosite.net

secret said...

From one day, I noticed that many people post a lot of articles related to 온라인슬롯. Among them, I think your article is the best among them!!I

reelgame.site said...

It’s actually great and useful information. Thank you for sharing this useful information with us. 슬롯머신

totosite365.info said...

Valuable info. Fortunate me I discovered your web site by chance. 스포츠토토

Unknown said...

Please let me know if you’re looking for a article writer for your site. You have some really great posts and I feel I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some material for your blog in exchange for a link back to mine. Please send me an email if interested. Thank you 먹튀사이트 I couldn't think of this, but it's amazing! I wrote several posts similar to this one, but please come and see!!

O.K Luan said...

I’m impressed, I have to admit. Truly rarely should i encounter a blog that’s both educative and entertaining, and without a doubt, you’ve hit the nail within the head. Your notion is outstanding; the pain is an issue that insufficient everyone is speaking intelligently about. I am very happy that we stumbled across this inside my try to find some thing relating to this. 메이저토토추천

casinosite.one said...

I’m now not positive the place you are getting your info, however great topic. 바카라사이트

survey- said...

Thank you for posting such a great information ……. LATEST SURVEY AND SWEEPSTAKES GUIDE

Unknown said...

I’m thinking some of my readers might find a bit of this interesting. Do you mind if I post a clip from this and link back? Thanks 먹튀검증

Dương Dương said...

I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you. 슬롯커뮤니티

Dượng đạt đây said...

That is the reason selling promoting efforts showcasing with the goal that you could significant investigate past advertisment. Simply to scribble down more grounded set that fit this depiction. 먹튀검증업체

GovtAssistances said...

Thankfully yes. There are various Dental Implant Grants available for individuals with low income and also low income families.

Unknown said...

ufabet1688x สมัคร เว็บแทงบอลออนไลน์ ยูฟ่าเบท ที่ได้มาตรฐานระดับโลก เว็บ UFABET มือถือ ที่ดีที่สุดในประเทศไทย แทงบอลออนไลน์ คาสิโน บาคาร่า สล๊อต ufabet

Unknown said...

บริการทุกระดับประทับใจ เลือกเล่น betflixsupervip มีหลากหลายเกมสล็อต ไม่ว่าจะเป็น สล็อต joker, pgslotgame , super-slot ที่หาเล่นได้ทั้งหมดจาก betflixsupervip ของเรา ทางเราเปิดบริการตลอด 24 ชั่วโมง ฝากถอนได้ดั่งใจ betflix

Unknown said...

ที่สุดแห่งบาคาร่าออนไลน์ biobetgaming บริการตลอด 24 ชั่วโมง บริการด้านเกมคาสิโนหลากหลายเช่น บาคาร่าออนไลน์ รูเร็ท แบล็คแจ็ค เสือมังกร ไฮโล และอื่นๆอีกมากมาย ที่มาพร้อมกับโปรโมชั่นโดนใจเน้นๆ ที่นี่ที่ biogaming

Dương Dương said...

I finally found what I was looking for! I'm so happy. 사설토토사이트 Your article is what I've been looking for for a long time. I'm happy to find you like this. Could you visit my website if you have time? I'm sure you'll find a post of interest that you'll find interesting.

Unknown said...

I finally found what I was looking for! I'm so happy. 안전한놀이터 Your article is what I've been looking for for a long time. I'm happy to find you like this. Could you visit my website if you have time? I'm sure you'll find a post of interest that you'll find interesting.