hi,

I have list of ids stored in datatable, I dont want to use filter and perform in query to get the entity collection but want to join the table; lets say document table with the datatable (I can create the table type with similar structure) and get the entity collection. I dont want to use In query because I have millions of rows in my table.

please suggest me how can I achieve this.

regards,

Bibek Dawadi

Do you meant that the Ids are stored in an ADO.Net datatable in memory, or actually in a table in the database?

Matt

yes ids are stored in an ADO.net datatable.

There is no way to join to a table structure in memory. The query against the ids will have to be done somewhere - either in the client code, or in an IN clause on the database.

You say the table has millions of rows - how many ids are in the datatable ? Have you actually tried using the IN clause to see what the performance is like ?

Matt

Hi Matt,

datatable can have couple hundred Ids (100-500), I just want to get those couple hundred entities more efficiently, using IN clause is not helping me much. Thus I am looking if I can use join clause.

or any other possible way to enhance the performance?

Thanks,

Hi Bibek,

With that amount of parameters you perfectly can use the IN clause (FieldCompareSetPredicate). Besides, if you just have values in-memory (in your datatable) the only option is to use FieldCompareSetPredicate (IN Clause). I don't know what do you mean when you say that you want to "join" your query (a db construct) to the datatable (in-memory values): AFAIK, there is no such thing in the world.

Maybe you just need to clarify that. If you can reproduce a subquery you want to join to, you can of course create a IN clause with subquery, or maybe a join, depending on your sql. Do you have an approximate sql you want to reproduce?

Hi Daelmo,

There is definately a way to join the table with user defined table type. Following is the sql query that I want to run through llblgen:

create type [dbo].[document_table] as table([document_id] int not null);

select dt.document_id from @documentTable dt left join document d on dt.document_id = d.document_id;

@documentTable is the parameter of type document_table which can accept datatable of similar structure; and can be supplied as sqlparameter via ADO.net.

document table is the real table with document_id as primary key.

can this be achieved via llblgen to get DocumentEntityCollection?

regards,

Bibek

This is not nativly supported. And as David has said, you can get the same results if you filter using a Range predicate (IN).

select dt.document_id from @documentTable dt left join document d on dt.document_id = d.document_id;

This particular query is not clear to me, as it will return all values passed for the DocumentId. You select nothing else but that and you perfrom a LeftJoin.

So if you can supply a more real query, we can help you formulate it with is available in the framework.

Hi Walaa,

select dt.document_id from @documentTable dt left join document d on dt.document_id = d.document_id;

that was just the example on how I can run the query joining ado.net datatable (using user defined table type) and database table as Daelmo is not aware about this.

All I want is to get all entities for the ids stored in @documentTable and I dont want to use IN clause as it degrades the performance in comparision with join clause. Another reason why I dont want to use IN query is, every seconds 100s of machines will be executing that query and I think using join clause I can have significant performance improvement.

If llblgen supports that query, please give me the way I can run the above query but get Entities instead of just getting ids.

regards,

Bibek

Hi Bibek. Sorry, as that is not supported. If you can code that in sql, you could create an stored procedure for that. Once you have that SP, you could project the SP results into entities (more info).

Thanks Daelmo.

I already have that query in stored procedure. How good is projecting SP results into entities against using IN Clause? Which one is more efficient where number of rows in the database is significantly very high?

regards

Bibek Dawadi

They're two seperate processes which can't really be compared - the IN query is a database operation, projecting SP results to entities happens in code after the query has been run.

All you can really do is try this, and see if the performance is adequate for your needs.

Matt

Thanks.