Writing Linq query considering delete indicators

Question

Hi

I have a requirement where I need to join 5 tables using a Linq query. All these tables have delete indicators.

In my main table if column1 is not deleted i need to get data irrespective of delete indicators in another tablesused with join keyword. I need to write a linq query such that if in other 5 tables the delete indicator is set, do not get value from that specific table and get values from other tables whose delete indicator is not "yes".

So how do i write a single join query for this requirement.

Thank you,

Best regards,

Usha.

Answer 1

Hi

It can be something like this:

var query = from e1 in ctx.Table1
                            join e2 in ctx.Table2 on e1.Id equals e2.Table1Id
                            join e3 in ctx.Table3 on e2.Id equals e3.Table2Id
                            join e4 in ctx.Table4 on e3.Id equals e4.Table3Id
                            join e5 in ctx.Table5 on e4.Id equals e5.Table4Id
                            where !e1.IsDeleted && !e2.IsDeleted && !e3.IsDeleted
                            && !e4.IsDeleted && !e5.IsDeleted
                            select new
                            {
                                e1.Name
                            };

---

http://sherifelmetainy.blogspot.com/

Answer 2

Hi Sherif Elmetainy,

Thank you for your reply. But in the above query if any one delete indicator is "Y" I will not get any results but my requirement is I need to still get data even if any indicator is "Y", probably I may not get the respective columns from the table if any delete indicator is "Y" in that table. Can you please suggest.

Thanks & Regards,

Usha 

Answer 3

Hi Usha2009;

In order to give a solution that will fit your needs please post the schema of all the tables and state what information you need returned from each table when what condition is meet.

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

I would write the main query to be a straight join on the main table to get a list of all records regardless of delete status, then run a query that performs a left join on all other tables and set the filter condition in each join (not the where) that considers the delete flag column. Then union them all together based on the records you get back from the second query so you still get the master record even if it is the one set to "deleted".

Answer 5

Hi Fernando,

Thank you for your reply.

Let us say I have a Linq query as below.

var query = (from table1 t1 in context. table1

                                 join table2 t2 in context.table2 on t1.column1 equals t2.column1

                                 join table3 t3 in context.table3 on t1.column2 equals t3.column2

                                 join table4 t4 in context.table4 on t1.column3 equals t4.column3

                 where (t1.column1 == 100) && t1.DeleteIndicator.Equals("N") && t2.DeleteIndicator.Equals("N") && t3.DeleteIndicator.Equals("N") && t4.DeleteIndicator.Equals("N")

Select new { id=t1.column1, name=t1.column2});

Here if any one table record is deleted, it is affecting the entire result and i get no records. My requirement is if records are not deleted, i should get values for those columns. If any record is deleted in any table still my end result should come but specific values from the deleted tables should be empty or no data.

Can you please suggest.

Thanks & Regards,

Usha

Answer 6

Hi Usha;

You state in your last post, "Here if any one table record is deleted", when you say "record is deleted" I will assume that the column "DeleteIndicator" in that record has a "Y" and NOT that the row in that table has been physically removed?

When using the ?: operator like this from the code snippet below :

(t2.DeleteIndicator.Equals("N")) ? t2.ColumnFromT2 : String.Empty,

both statements in the true and false parts must return data of the same type. In the above statement t2.ColumnFromT2 must return a string because the false part is returning a string.

var query = (from table1 t1 in context.table1
             join t2 in context.table2 on t1.column1 equals t2.column1
             join t3 in context.table3 on t1.column2 equals t3.column2
             join t4 in context.table4 on t1.column3 equals t4.column3
             where (t1.column1 == 100) && t1.DeleteIndicator.Equals("N") 
             select new 
             { 
                 id = t1.column1, 
                 name = t1.column2,
                 ColumnFromT2 = (t2.DeleteIndicator.Equals("N")) ? t2.ColumnFromT2 : String.Empty,
                 // ...
                 ColumnFromT4 = (t4.DeleteIndicator.Equals("N")) ? t4.ColumnFromT4 : -1
             });

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 7

Hi Fernando,

Thanks for your reply. So for any confusion. I mean the delete indicator is set to "Y" in any of the records in the tables instead of delete record still I should get the result data from the main query. So how should i make changes to my query.

Since i added all ampersand so the "and" condition will not give any data right. So how should i change the query where if a record delete indicator "Y" is set, i will notget any data from that table but the join query gives data from other tables.

Can you please suggest.

Thanks & Regards,

Usha

Answer 8

  

OK lets try it this way because I am not understanding the requirements you have. 

From your statement, "So how should i change the query where if a record delete indicator "Y" is set, i will notget any data from that table", I am assuming that you are talking about Table1 here, correct?  Then use the ?: operators as I showed for joined tables from my previous post for the columns in Table1.

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".